Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting A-Z then AA-AZ etc
Possibly the wrong place for this question, but I am after the best way to
sort a set of data by reference to a column that has 1 or 2 alphabetic entries (ie A-Z, AA-AZ, BA-BZ, etc). On its own excel sorts as A, AA,AB - AZ, B, BA etc. Any suggestions gratefully received. Is this just a job for bubble sorting? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting A-Z then AA-AZ etc
sort a set of data by reference to a column
no sure what you mean here. However: it seems like you want to sort by: 1-length (1 or 2: the length of the string A,B,...AA,AB...) 2-then by alphabetical order Assuming your data (A,B,...AA,AB...)is in column A starting row 2. Currently, Excel would sort as: A AA AB .... B BA .... So, in another column, row 2, enter the formula: =len(A2)&A2 len() returns the length (1 if A,B,... and 2 if AA, AB,...) so now, instead of A,B,...AA,AB,... you have: 1A,1B,...,2AA,2AB That is, if you sort by this new column, excel should first sort the number (1 or 2) then by the letter (A,B,...): 1A 1B .... 2AA 2AB .... Would that work for you? Regards, Sebastien "Alan" wrote: Possibly the wrong place for this question, but I am after the best way to sort a set of data by reference to a column that has 1 or 2 alphabetic entries (ie A-Z, AA-AZ, BA-BZ, etc). On its own excel sorts as A, AA,AB - AZ, B, BA etc. Any suggestions gratefully received. Is this just a job for bubble sorting? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting A-Z then AA-AZ etc
Sebastien
Thanks, that'll do it. Obvious when you think about it, and annoying that I couldn't come up with it myself - having done the hard bit sorting out the code to pick up all the details to create the list from other files ! :-) Thanks again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Values Without Sorting Formulas | Excel Discussion (Misc queries) | |||
Automatic sorting (giving max and min) based on custom sorting lis | Excel Worksheet Functions | |||
Sorting VLookup vs Sorting SumProduct | Excel Discussion (Misc queries) | |||
Sorting: Sorting by the First Character | Excel Discussion (Misc queries) | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |