More Sorting Multiple Columns
In Sort On More Than Three Columns, I described how to short your data in two steps. Mark’s tip for sorting on more than three columns uses a helper column.
Using another column, you can concantenate the columns you want to sort and sort on that column. This example sorts on four columns by using a fifth column to string together the other four.
There is a problem with this method. In the screen shot below, Dick’s week 5 entry is sorted above John’s. The ‘Number’ column is coverted to text in the concatenation process and 18 is less than 5 when their both considered to be text. Text is sorted by comparing characters from left to right.
So be careful if you use this method. Thanks for the tip, Mark.
Tom:
Use the TEXT formula to format the numeric field and for the Aplabetic Fields use the REPT function to append spaces to the end.
=TEXT(B2,”00″)&TEXT(C2,”000″)&TEXT(D2,”00″)&A2&REPT(” “,20-LEN(A2))
Name Week Amount Number Sort
8 November 2004, 12:51 pmJohn 2 600 16 0260016John
Tom 3 300 19 0330019Tom
Dick 4 100 14 0410014Dick
John 4 200 12 0420012John
Dick 5 300 18 0530018Dick
Tom 5 800 14 0580014Tom
John 6 500 5 0650005John
Tom 7 100 9 0710009Tom
Dick 8 400 19 0840019Dick
ross:
Dick, did you ever get anywhere with the fuzzy sort thing you posted a while back - i had a quite look one day (when i needed to do something similar) but could’t reallt offer anything new.
8 November 2004, 2:04 pmJon Peltier:
Tom’s approach is also useful to set up a lazy-man’s multi column lookup.
- Jon
8 November 2004, 6:25 pmZoltan Till:
This is also useful for multi conditional SUMIF and COUNTIF.
10 November 2004, 9:49 amI used many times.
Zoltan
C.G.:
I agree with all the above.
I use multi-column concatenations all the time for sorting, lookup and area formula purposes. It works like a charm but as discussed above it has to be set up properly. For example, if one column is expected to have numbers of one to five digits, then those numbers have to left-padded with zeros with Text(b2,”00000″) …
11 November 2004, 12:14 pm