View Single Post
  #2   Report Post  
Héctor Miguel
 
Posts: n/a
Default

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.