Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting more than 3 columns
Hi, Does anyone know if its possible to sort data by more than the 3 criteria in the sort menu in DataSort? Preferably I need to do it on 5 columns, I can't figure it out though - Would I need to use a pivot table? Thanks for any help at all. Regards, saybut. -- saybut ------------------------------------------------------------------------ saybut's Profile: http://www.excelforum.com/member.php...fo&userid=5949 View this thread: http://www.excelforum.com/showthread...hreadid=514329 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting more than 3 columns
Hi, sorry I've just figured out how to do it. Just in case anyone is interested, if you're sorting five columns, A,B,C,D,E sort the last three first, C,D,E and then sort A,B. or if anyone has any better ways let me know. thanks. -- saybut ------------------------------------------------------------------------ saybut's Profile: http://www.excelforum.com/member.php...fo&userid=5949 View this thread: http://www.excelforum.com/showthread...hreadid=514329 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting more than 3 columns
No, that is the way to do it.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "saybut" wrote in message ... Hi, sorry I've just figured out how to do it. Just in case anyone is interested, if you're sorting five columns, A,B,C,D,E sort the last three first, C,D,E and then sort A,B. or if anyone has any better ways let me know. thanks. -- saybut ------------------------------------------------------------------------ saybut's Profile: http://www.excelforum.com/member.php...fo&userid=5949 View this thread: http://www.excelforum.com/showthread...hreadid=514329 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting more than 3 columns
Another way is to join columns together in a helper column, eg in F2
enter the formula: =A2&B2&C2&D2 &E2 copy down, and sort on this one field. The fields should be converted to fixed width if they are not already. If the field is text then spaces can be added to make it up to a fixed number of characters, and if it is numeric it can be made fixed width using TEXT( ). So you might actually have something like: =A2&B2&REPT(" ",20-LEN(B2))&C2&REPT(" ",10-LEN(C2))&TEXT(D2,"000")&E2 Here A2 is already fixed width (eg code number), B2 is made up to 20 characters, C2 is made up to 10 characters, D2 is a number fixed to 3 characters and E2 is not changed. The advantage is only one sort is needed. Of course, this leaves two other slots in the sort dialogue box for further refinement. Hope this helps. Pete |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting more than 3 columns
Thanks a lot for the posts. At the moment I'll just stick with the orignal method. Although it doesn't seem to work sort from the back to the front more than twice - maybe just me though. thanks again. -- saybut ------------------------------------------------------------------------ saybut's Profile: http://www.excelforum.com/member.php...fo&userid=5949 View this thread: http://www.excelforum.com/showthread...hreadid=514329 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting columns with expressions | New Users to Excel | |||
sorting data across multiple columns | Excel Discussion (Misc queries) | |||
Columns | Excel Discussion (Misc queries) | |||
SORTING question | New Users to Excel | |||
Sorting rows AND columns | Excel Worksheet Functions |