Excel: Sorting columns A to I - Is it possible?

[mmtraining]

I need to sort a table in Excel. No great problem, but... I have to sort by column A then within that by B then within that by C and so on to column I.

Is this physically possible? I have never tried it.

Do I just record the steps to sort using three columns and then copy the code until I have enough?

Has anybody done this before?

Thanx for any help

Carol
Berlin, Germany

 

[Draig]

Hi Carol
If you record yourself using the Data, Sort command, in its dialogue box you can specify upto 3 columns to sort on. That will give you the basic VBA code you need for what you want to do. Barborne
Worcester
UK

 

[RobBroekhuis]

The answer is no - you can't sort on more than three columns very easily. If you really need to, you can create a temporary column with the concatenated values of columns C through I, and use it as your third sort key; something like:

formula in column Z:
=concatenate(C1,"@",D1,"@",E1,"@",F1,"@",G1,"@",H1,"@",I1)

then sort on A,B,Z
The "@"s are in there to make sure that things sort OK in situations like:

C D
1 Mickey Mouse
2 Mick Jagger

You want line 2 to sort before line 1; without the @s, the opposite would happen.
Rob

 

[mmtraining]

Thanks, Barborne, I tried it out in the hope it would work and discovered that the VBA code also keeps us to the three sorting criteria. Nice thought from both of us, sad it doesn't work.

Rob, I haven't tried it out yet, but it sounds like the only way of doing it. If it works (will take the table home and try it out) then I will cover you with a star on Monday. Thanks especially for the tip about the @ symbol, haven't heard that before.

 

[mscallisto]

Rob

I tried with and without the "@"'s and noticed Mick came first both ways

Mick Jagger Mick@Jagger MickJagger
Mick Jagger Mick@Jagger MickJagger
Mickey Mouse Mickey@Mouse MickeyMouse

What am I missing?

sam

 

[Zathras]

Rob's formula won't work as given unless it is true that for each column, the data in each row have the same length.

For example, if C1 contains "abc" and D1 contains "def" and C2 contains "ab" and D2 contains "bghi" then you will be sorting "abbghi" against "abcdef" which is probably not what you want. Similarly, numbers may not all have the same number of digits (and some may be negative).

For example, if C1 contains text, then in place of C1, you can substitute this expression:
LEFT(C1&REPT(" ",9),9)
where the number (9 in this example) is the length of the longest text string in the column.

And for example if D1 contains a number, then in place of D1, you can substitute this expression:
10000+D1 where the number (10000 in this example) is larger than the largest number (absolute value) in the column.

HTH

 

[mscallisto]

Yes I agree...it is and has been the standard ascii sort relation rule, nulls=0 space=32 A=65 etc. (base 10)

It's just the fact that it was Rob's post lead me to believe it was an "excel/(vba)" thing that I hadn't heard of.

 

[combo]

If your columns have unique values, or, at least, for sorted columns 1 to n rows in columns 1-n are unique (n=1 to 10), you can use pivot table for sorting.
Drag columns 1 to 10 as row fields, as data field - any (only to avoid error). Set 'subtotals' for fields to 'none' and uncheck 'totals' for whole table. If it is not automatic or want descending, you can set sorting mode in 'advanced' setting of row field.

If the firstly nentioned condition fails - you will get also sorted table, but with empty areas. To remove them, after copying data only to separate sheet a simple macro can fill empty areas in the output table (you can't do so in pivot table directly).

 

[RobBroekhuis]

Zathras,
Actually, I think my code will work (because of the "@"s in between). Can you think of an example where it doesn't?
Rob

 

[RobBroekhuis]

Sam,
The reason why the Micks got sorted that way is probably because of case-sensitive sorting. (where J could be smaller than e) Not sure though.
Rob

 

[mscallisto]

Rob

I sorted my example A,B,C then A,B,D and got the same results.
A B C D
Mick Jagger Mick@Jagger MickJagger
Mick Jagger Mick@Jagger MickJagger
Mickey Mouse Mickey@Mouse MickeyMouse

Again am I simply not seeing something?
I hate to miss out on a neat trick!

Sorry I can't get the columns to line-up on the post

 

[RobBroekhuis]

Sort on JUST column C or D. Sorting on columns A and B as primary and secondary keys means that column C has no influence on the sort order. Or, for a more complete example that more closely mimics the original problem statement, consider:


A B C D
1. Donald Duck Mick Jagger
2. Donald Duck Mickey Mouse
3. Daisy Duck Mickey Mouse

Now if you sort on A, then B, then (C@D), the order should be 3, 1, 2. I.e. only if columns A and B are identical does column C matter.

Rob

 

[Zathras]

Rob, I think you are right for text columns (as long as the text doesn't contain the "@" sign as you implied). However, numbers will still be a problem (10003 sorts before 99 for example and the sign on negative numbers appears to be ignored.)

 

[mscallisto]

The THICK one get's it!
How can I remove my previous responses (embarrassed).

I was thrown off-course by your "then sort on A,B,Z
"

sam

 

[RobBroekhuis]

Zathras,
You're absolutely right. For numeric columns, you'd need to use a leading-spaces format, and it still wouldn't work for negative numbers. By the way, the text data can include "@"s, that won't mess up the sorting: the sort doesn't try to parse. The reason for using @ is that it sorts before alphabetic characters. Many other characters, including space, would work just as well.
Rob

 

[euskadi]

This function will give you a string 12 characters long with the rank of each item in your range, using 4 columns. Add this as your sort column, with the appropriate ranges, filldown, and sort by this column.

You can extend this out to 14 columns I'm sure, and if you think you'll have more than 999 items just add some zeroes to the "000" string.

=TEXT(COUNTIF(A$2:A$7,&quot;<=&quot;&A2),&quot;000&quot&TEXT(COUNTIF(B$2:B$7,&quot;<=&quot;&B2),&quot;000&quot&TEXT(COUNTIF(C$2:C$7,&quot;<=&quot;&C2),&quot;000&quot&TEXT(COUNTIF(D$2$7,&quot;<=&quot;&D2),&quot;000&quot