Excel - sort by 4 columns 2

[Andrzejek]

After a search that turn nothing (well, I did find some samples of how to sort by 4 columns, but it does not work) the question is:

How to sort data in Excel 2003 by more than 3 columns? - I need to sort by 4 columns.

Like I said, one suggestion was to sort by 4th column first, then sort by 3 other ones and Excel will keep the first sort. But it does not work.

Have fun.

---- Andy

 

[SkipVought]

Hi,

Did you know that before PC's and Excel, people sorted punch cards with up to 80 columns of data with a ONE COLUMN sorter?

The "trick" is to sort from the LEAST significant postion to the MOST significant position.

"...one suggestion was to sort by 4th column first, then sort by 3 other ones and Excel will keep the first sort. But it does not work."

Ahhhhh, but it does.

Please post an EXAMPLE of the data you are sorting that does not seem to work and describe the data that is in each of the four columns.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[PHV]

Something to try is to sort by the 3 least significant columns and then by the 3 most significant.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Another way is to sort by a helper column made of a properly formatted concatenation of the 4 columns.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

OK, here is what I have in code for sorting:

    Range("A1:AB103").Sort Key1:=Range("G2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers

    Range("A1:AB312").Sort Key1:=Range("B2"), Order1:=xlAscending, _
        Key2:=.Range("C2"), Order2:=xlAscending, _
        Key3:=.Range("E2"), Order3:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom, DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortNormal _
        , DataOption3:=xlSortNormal

First sort is by Account ID, and then by District, RCE, and Contract ID.

Rows in [red]RED[/red] is what I get, and they are NOT in order:
023750
023751
023749
023752

Have fun.

---- Andy

 

[SkipVought]

What are these ranges DIFFERENT???

    [b]Range("A1:AB103")[/b].Sort Key1:=Range("G2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
[b]
    Range("A1:AB312")[/b].Sort Key1:=Range("B2"), Order1:=xlAscending, _
        Key2:=.Range("C2"), Order2:=xlAscending, _
        Key3:=.Range("E2"), Order3:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom, DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortNormal _
        , DataOption3:=xlSortNormal

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[SkipVought]

You also have a With Sheet something that you did not post.

ALL the range reference ought to reference back to the sheet

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[PHV]

Furthermore, Account ID is F2 (not G2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

After I posted it I look very close at my code and - doh, I do try to sort on F column, but in my code I had G column. This is fixed and works OK now.

Thanks for all your help


Have fun.

---- Andy