Multiple sorting criteria

[Kysteratwork]

Hi,

I would have to sort a large excel sheet by 4 criteria . Using the VBA recorder below is the code, but only for 3 criteria - if I add a fourth, I get an error message... is it not possible to have 4 criteria??

Thanks in advance for any indication you can provide.

KaW

Dim i As Integer

i = MsgBox("Sort the Meetings Database?", vbOKCancel, "Meetings Database")
If i = vbOK Then
   
    
    Sheets("MeetingsData").Select
    Range("A2").Select
    Selection.CurrentRegion.Select
    Selection.Sort Key1:=Range("L3"), Order1:=xlAscending, Key2:=Range("H3") _
        , Order2:=xlAscending, Key3:=Range("F3"), Order3:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
        End If

'And I added: Key4:=Range("B3"), Order4:=xlAscending

 

[SkipVought]

Hi,

The old Hollerith card sorters only sorted ONW column at a time. Consequently, you sorted from the LEAST significant character to the MOST significant character.

It works the same way for Excel columns. Sort the 4[sup]th[/sup] column FIRST and then the remaining 3.

Skip,
[sub]
[red][/red]
[/sub]

 

[Kysteratwork]

Hi SV,

Thanks for your reply.. but I am not sure I understand. Can I integrate the fourth (or first) criteria into the same code range? It's of course no point me sorting by one criteria, followed by the set of three (which would nullify the first).

Sorry if I sound daft.

KaW

 

[SkipVought]

" It's of course no point me sorting ...."

This is how ANY sorting works -- LEAST to MOST significant. That's how Excel does 2 or 3 behind the scenes. You could sort 100 column that way.

It may not make sense to you, but it does not have to. That's the way it works!

Skip,
[sub]
[red][/red]
[/sub]

 

[AnotherHiggins]

It's of course no point me sorting by one criteria, followed by the set of three (which would nullify the first).

Doing the set of three does not "nullify the first". The changes made to the dataset by doing the "set of three" is applied to the dataset as it is sorted after the "first" sort has been done.

Get it?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[stevexff]

While we're going back to Hollerith, it sounds like this exploits a feature of the MS sort routine, which must work like the old IBM SORT "EQUALS" option. You sort on column 4, which orders the data set. You sort on columns 1 through 3. If any of these sort equal, then the original ordinal position in the data set is used as the order within the first three fields, effectively giving you a sort on 4 fields.

Another possibility is to add a column on the end that catenates the fields together, then sort on that. But this would probably be a bit fragile, especially if you have variable length strings, numerics etc. in the mix...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[AnotherHiggins]

Steve: What you said in your first paragraph - Yes, that's the way it works.

In fact, this is all explained if you search Excel's help file for the word "SORT".

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Kysteratwork]

Stevecff,

That's indeed the way I am now doing it and it seems to work.

Thank you all.

Regards,
KaW