Spent much time looking for a way to sort this data set (which is in an Excel worksheet table as part of a much larger dataset) such that it's in order from A to Z and of course all of the rows in the table sort accordingly.
Attempted to:
1. Format the column at "Text" and manually add each string, once cell at a time.
2. Format the column as "Custom" and selected custom format "000" and entered each string manually as well as copy & paste.
3. Use "Text to Columns" with the column containing the data set as the source.
This is how the data set appears after formatting the column as "Text", manually entering each string, and sorting A-Z. Notice that they strings are NOT properly sorted. Scientific Notation seems to be problematic (see string 7E1) and numeric stings are not properly indexed and interleaved with alphanumeric strings:
011
023
034
7E1
085
107
229
270
360
420
501
627
901
09G
0Z5
39Z
3G5
41N
55Q
6DZ
7FF
82A
98E
99R
Interestingly enough, the Excel "Filter" dropdown shows the dataset very nicely indexed in the proper order.
Any ideas on how to accomplish the same as the indexing appears in the Filter dropdown? The actual dataset is MUCH larger than the sample set illustrated here. Any assistance would be greatly appreciated!
PS Using formulas probably (?) won't work because the dataset is in a large table and is just one of the many dynamic sort criteria. In other words, sometimes the table might be sorted on this dataset, other times it may be sorted on other columns, or there might be multiple sort criteria. It's not a matter of just sorting the data for a single instance.
I pasted your sample data into an Excel sheet formatted as text, then sorted on the Code column:
Perhaps you can attach a sample workbook in a private message to me (click on my user picture)
Accepted Solutions