Sorting multiple columns in VBA

[Mahi]

I'd like to sort multiple columns in VBA (Excel 2003). That's not a
problem you'd think, but the columns are not placed next to each other.
There are columns in between which may NOT be sorted!

Since a simple example says more than words... Take following table:

[A] B [C] D [E]
+-------+-----------+-------+---------+-------+
1 | B | =IF(A1... | 12| =C1*... | 75|
2 | D | =IF(A2... | 11| =C2*... | 70|
3 | A | =IF(A3... | 19| =C3*... | 80|
4 | C | =IF(A4... | 20| =C4*... | 40|

We want to sort columns A, C and E alphabetically according to the data
in column A, but leave B and D alone. This should lead to:

[A] B [C] D [E]
+-------+-----------+-------+---------+-------+
1 | A | =IF(A1... | 19| =C1*... | 80|
2 | B | =IF(A2... | 12| =C2*... | 75|
3 | C | =IF(A3... | 20| =C3*... | 40|
4 | D | =IF(A4... | 11| =C4*... | 70|

Selecting columns A, C and E without selecting B and D isn't a problem:

Range("A1:A4,C1:C4,E1:C4").Select

But when I try a sorting construction like shown below, I get an error:

Range("A1:A4,C1:C4,E1:C4").Sort Key1:Range("A1"), Order1:xlAscending

I'm not sure why though... Anyone knows a way to accomplish what I want?

 

[Tom Ogilvy]

obviously all your formula is not visible, but based on what is showning,
you should be able to sort A:E (including B and D) and get the result you
want. The formulas (relative references) will adjust.

 

[Mahi]

obviously all your formula is not visible, but based on what is showning,
you should be able to sort A:E (including B and D) and get the result you
want. The formulas (relative references) will adjust.

But that's exactly what I don't want to happen...

 

[Tom Ogilvy]

You want B2 for example to continue to refer to the old value of A2, which
in your example would be A4. Is that correct.

 

[Guest]

I entered the data as you show in your upper example into a new sheet.
I selected the range A1:E4 and clicked the Ascending sort button
and I get the result you are showing in the lower table as you requested.

Perhaps your formulas are not producing what you want. Since you are
showing Relative references in your formulas, maybe you want them to be
absolute. If so,
click on a cell with a formula. Then in the formula bar, click on a
reference, say A1 to turn it blue, then click the F4 key to cycle thru the 3
forms. An $ will make that part Absolute

Does this help?

 

[Mahi]

You want B2 for example to continue to refer to the old value of A2, which
in your example would be A4. Is that correct.

No, B2 should refer to A2, before sorting... and after sorting. Column B
and D shouldn't be altered in any way. No reference updates!

 

[Tom Ogilvy]

Sorting A:E should do what you want. In fact it is almost impossible not to
do what you want. Again, that is if your formulas are row centric - they
just refer to the row in which they are located.

 

[Tom Ogilvy]

In my test, making them absolute or relative didn't change the end result
for row centric formulas.

--
Regards,
Tom Ogilvy

I entered the data as you show in your upper example into a new sheet.
I selected the range A1:E4 and clicked the Ascending sort button
and I get the result you are showing in the lower table as you requested.

Perhaps your formulas are not producing what you want. Since you are
showing Relative references in your formulas, maybe you want them to be
absolute. If so,
click on a cell with a formula. Then in the formula bar, click on a
reference, say A1 to turn it blue, then click the F4 key to cycle thru the 3
forms. An $ will make that part Absolute

Does this help?

I'd like to sort multiple columns in VBA (Excel 2003). That's not a
problem you'd think, but the columns are not placed next to each other.
There are columns in between which may NOT be sorted!

Since a simple example says more than words... Take following table:

[A] B [C] D [E]
+-------+-----------+-------+---------+-------+
1 | B | =IF(A1... | 12| =C1*... | 75|
2 | D | =IF(A2... | 11| =C2*... | 70|
3 | A | =IF(A3... | 19| =C3*... | 80|
4 | C | =IF(A4... | 20| =C4*... | 40|

We want to sort columns A, C and E alphabetically according to the data
in column A, but leave B and D alone. This should lead to:

[A] B [C] D [E]
+-------+-----------+-------+---------+-------+
1 | A | =IF(A1... | 19| =C1*... | 80|
2 | B | =IF(A2... | 12| =C2*... | 75|
3 | C | =IF(A3... | 20| =C3*... | 40|
4 | D | =IF(A4... | 11| =C4*... | 70|

Selecting columns A, C and E without selecting B and D isn't a problem:

Range("A1:A4,C1:C4,E1:C4").Select

But when I try a sorting construction like shown below, I get an error:

Range("A1:A4,C1:C4,E1:C4").Sort Key1:Range("A1"), Order1:xlAscending

I'm not sure why though... Anyone knows a way to accomplish what I want?

 

[Mahi]

I entered the data as you show in your upper example into a new sheet.
I selected the range A1:E4 and clicked the Ascending sort button
and I get the result you are showing in the lower table as you requested.

Take for example following data (tabbed so you can enter it directly in
Excel):

B =IF(A1="A";"yes";"no") 11 =C1-12
D =IF(A2="B";"yes";"no") 20 =C2-11
A =IF(A3="C";"yes";"no") 12 =C3-19
C =IF(A4="D";"yes";"no") 19 =C4-20

This will show:

B no 11 -1
D no 20 9
A no 12 -7
C no 19 -1

What I want is the following; sorting the first and third column
according to the first column, but leave the formulas in the second and
fourth column intact. This gives:

A =IF(A1="A";"yes";"no") 12 =C1-12
B =IF(A2="B";"yes";"no") 11 =C2-11
C =IF(A3="C";"yes";"no") 19 =C3-19
D =IF(A4="D";"yes";"no") 20 =C4-20

And will show:

A yes 12 0
B yes 11 0
C yes 19 0
D yes 20 0

But... If I sort "A14" the situation becomes:

A =IF(A1="C";"yes";"no") 12 =C1-19
B =IF(A2="A";"yes";"no") 11 =C2-12
C =IF(A3="D";"yes";"no") 19 =C3-20
D =IF(A4="B";"yes";"no") 20 =C4-11

Notice how columns B and D differ from what I want. This will show:

A no 12 -7
B no 11 -1
C no 19 -1
D no 20 9

Which is of course not what I want!

Adding absolute positions ($) will not change anything.

I don't get why this doesn't seem to be possible with Excel. You can for
example sort multiple columns by using "A1:B4" (will sort A and B, but
not C and D), but you cannot specify "A1:A4,C1:C4" (to sort A and C but
not B and D)? Where's the logic?

It may seem like a strange request, but I really need this. Is there
another way to sort the data using VBA so I will get what I want?

 

[Mahi]

Sorting A:E should do what you want. In fact it is almost impossible not to
do what you want. Again, that is if your formulas are row centric - they
just refer to the row in which they are located.

Please take a look at the (better) example I posted in reply to
"gocush". I hope that explains the situation a bit better.

 

[Tom Ogilvy]

With your sample date run this:

Sub Macro7()
Columns("B:B").Select
Selection.Cut
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Range("A1:B4").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("E:E").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("A4").Select
End Sub

Use this concept to fix you problem (cut the columns you don't want sorted,
sort the data, cut the columns not sorted back to their original locations )

Write code to sort as Excel now does and that will answer your question on
why you can't just do what you want.

 

[Dick Kusleika]

Mahi

Whenever I have this situation, I try to make my formulas row-centric so
they can be sorted. For instance from your example, you may use another
column outside of where you're sorting and put

A
B
C
D

and change your formulas to

=IF(A1=G1,"yes","no")

Since G isn't part of the sort, it will remain in the order A,B,C,D and
although your formulas will be sorted, they will still return the correct
results.

Another way would be to change your formulas to

=IF(A1=CHAR(64+ROW()),"yes","no")

That works because your condition can be created on the fly with the row
number.

I realize this is a simple example and that your actual situation is
probably quite a bit more complex. You can get creative and convert your
formulas to row-centric formulas, but be warned that you'll probably end up
with really ugly formulas.

If you think this is a suitable solution and need help converting your
formulas, post back with a sample of the actual formula.

 

[Mahi]

With your sample date run this:
Sub Macro7()
Columns("B:B").Select
[..]
Range("A4").Select
End Sub
Use this concept to fix you problem (cut the columns you don't want sorted,
sort the data, cut the columns not sorted back to their original locations )

I somewhat hoped there would be an easier way to make the Excel sort
function behave the way I want, but your technique works fine too. "Dick
Kusleika" posted another method I'll take a closer look at. It seems to
me the best solution at this point.

Thanks Tom, Dick and gocush for your input!