Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sorting alphanumeric
hello, I hope this problem I am having can be simply solved....
I need to sort all in one column the following sequence ( I use it for managing keys that are made. Subsequent keys are enumerated by adding the next sequential letter) ex: 101, 102,102a,102b,103 Current any sort I use sorts it this way :101, 102, 103, 102a, 102b What can I do to sort chronologically with the letters in the same column so the result looks like this: 101, 102,102a,102b,103 If I add a key to 101, it needs to sort this way: 101,101a,102,102a,102b,103 can any one help, thank you |
#2
|
|||
|
|||
hi, Chronos !
... I hope this problem... can be simply solved... I need to sort all in one column the following sequence... ex: 101, 102,102a,102b,103 Current any sort I use sorts it this way :101, 102, 103, 102a, 102b ... to sort chronologically with the letters in the same column so the result looks like this: 101, 102,102a,102b,103 If I add a key to 101, it needs to sort this way: 101,101a,102,102a,102b,103 one -possible- way and if it's ok for you to 'have to' use a 'helper' column... assuming first key in [b2], sort by a column with a formula like the following: =sumproduct(value(code(mid(b2,choose(1+(len(b2)=4) ,{1;2;3},{1;2;3;4}),1))&rept("0",choose(1+(len(b2) =4),{7;5;3},{7;5;3;0})))) if you are planing to generate keys including letters for the first 3 characters... you might have to change the 'zero' array from: 7;5;3 to: 10;7;4 hth, hector. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting alphanumeric numbers | Excel Discussion (Misc queries) | |||
Sorting alphanumeric | Excel Discussion (Misc queries) | |||
sorting an alphanumeric list - please someone help! | Excel Discussion (Misc queries) | |||
alphanumeric sorting | Excel Discussion (Misc queries) | |||
sorting number in ascending order | Excel Discussion (Misc queries) |