Sort on no less than sixteen keys

[Cooz]

Hi everyone,

I have a worksheet with a table that contains twenty columns. Four of them
have numeric data. The challenge I face is to sum the data per column in
these four columns for which the data in the other sixteen columns is the
same on each row.

For example:

(row 1) AA ZZ 03 DD FF 10 GG
(row 2) AA ZZ 05 DD FF 02 GG
(row 3) AA ZZ 04 DD EE 05 GG
(row 4) AA ZZ 04 DD EE 01 GG
(row 5) AA ZZ 08 DD FF 02 GG
(row 6) AA ZZ 09 CC FF 01 GG

I must add up the two columns 3 and 6...
- of rows 1, 2 and 5
- of rows 3 and 4
- of row 6
.... because the data in the other five columns of these rows match. Instead
of these two plus five columns, I must deal with four plus sixteen.

To do this, what I need is a sort on sixteen keys -because I need to be
aboslutely sure that all data is in a specific order- check the rows one by
one and perform the calculation whenever a change in one of these keys
occurs. I know how to do the check, but I don't know how to do the sort.

- Is sorting on no less than sixteen keys possible with VBA, and if so, how?
- Can this task perhaps be performed in an easier way that I'm overlooking?

Thank you,
Cooz

 

[Joel]

Excel in both the worksheet and VBA can only sort on 3 keys at one time. You
can perform multiple sorts when you need more than 3 keys. Excel maintains
the order from previous sorts. You run the sorts backward form the lowest
precedence to the higherest.

If your sort order is from Columns 1 to 20. First sort columns 19 & 20.
Then 16 - 18. Next 13 - 15, 10 - 12, 7 - 10, 4 - 6, and then 1 - 3.

the code bel;ow will sort 21 columns. i added one column to make the code
simplier. The 21 column is just a blank colun. You can change the sort
order by changing the array in the code below.

Sub Macro1()
'
Dim SortOrder As Variant
SortOrder = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", _
"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U")

Set sortRange = Range("A1:U100")
For i = 18 To 0 Step -3
sortRange.Sort _
Key1:=Range(SortOrder(i) & 1), _
Order1:=xlAscending, _
Key2:=Range(SortOrder(i + 1) & 1), _
Order2:=xlAscending, _
Key3:=Range(SortOrder(i + 2) & 1), _
Order3:=xlAscending, _
Header:=xlGuess
Next i
End Sub

 

[Bernd P]

Hello,

Why don't you insert a helper column which concatenates columns
1,2,4,5 and 7, then sort by that helper column and apply DATA |
SUBTOTALS?

Regards,
Bernd

 

[Cooz]

Thank you Joel, this answer works fine.

Cooz

Excel in both the worksheet and VBA can only sort on 3 keys at one time. You
can perform multiple sorts when you need more than 3 keys. Excel maintains
the order from previous sorts. You run the sorts backward form the lowest
precedence to the higherest.

If your sort order is from Columns 1 to 20. First sort columns 19 & 20.
Then 16 - 18. Next 13 - 15, 10 - 12, 7 - 10, 4 - 6, and then 1 - 3.

the code bel;ow will sort 21 columns. i added one column to make the code
simplier. The 21 column is just a blank colun. You can change the sort
order by changing the array in the code below.

Sub Macro1()
'
Dim SortOrder As Variant
SortOrder = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", _
"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U")

Set sortRange = Range("A1:U100")
For i = 18 To 0 Step -3
sortRange.Sort _
Key1:=Range(SortOrder(i) & 1), _
Order1:=xlAscending, _
Key2:=Range(SortOrder(i + 1) & 1), _
Order2:=xlAscending, _
Key3:=Range(SortOrder(i + 2) & 1), _
Order3:=xlAscending, _
Header:=xlGuess
Next i
End Sub

 

[Cooz]

Thank you Bernd. Your answer is short and simple, and works magnificently.

Cooz