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 |
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. |
All times are GMT +1. The time now is 05:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com