Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel's Sort Order
After much work, I would like to seek advice and comments regarding
Excel's sort order. I presume that Excel has its own rules (running in the background) that enable the standard worksheet sort to sort alphabetically & be case sensitive ... however, VBA code (using Option Compare Binary or Text, as required) seems to use the ANSI character set, which puts "A" before "a" when Binary is set. This would therefore imply that other sort routines that are based on the ANSI set (QuickSort, MergeSort) cannot sort truly alphabetically (in Excel VBA) .... or more realistically, as I would like to sort. For instance, a case sensitive Excel gives the order AAaa, AAaA, AAAa ( a left to right, character by character comparison, "a" before "A"), whereas a QuickSort with Option Compare Binary gives AAAa , AAaA, AAaa. With Option Compare Text, QuickSort says all 3 are equal, and the eventual sort order seems dependent on the original list/order …. and looking at how QuickSort works, I'm not surprised! A possible solution as, I understand it, is to split the data into sequential alpha-numeric sections and then either : * Use a worksheet to sort the sections from the right to the left. The sort is then stable, and advantage is taken of Excel's internal rules that enable a true alphabetical, case sensitive sort. It can be made to be remarkably quick, as long as the sections (ie columns) do not become excessive. * Pad numbers, rebuild into a single entity and sort on this data. A stable sort is required to ensure the original data follows … It is worth noting that Excel also has its own oddities, ie how it handles the "." and "-" characters … it sure makes life hard when trying to sort, and complicates the process of splitting into the alpha-numeric groups. Even after the splitting/padding/concatenating, I still cannot get a standard QuickSort to sort "correctly", as it seems to be based on the ANSI character set, as noted above. Any comments would be most welcome. Regards, Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
First key sort order | Excel Discussion (Misc queries) | |||
Retaining Sort order in the Sort Dialog box | Excel Discussion (Misc queries) | |||
Sort sheet based on particuilar sort order | Excel Worksheet Functions | |||
sort last name order | Excel Discussion (Misc queries) | |||
sort order | Excel Discussion (Misc queries) |