SOLVED

Sorting Scientific Notation, Alpha, Numeric, and Alphanumeric

Brass Contributor

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.

Picture1.jpg

 

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.

4 Replies

@trs1111 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar 

Hi Hans, 

 

Thanks for taking a look at this. The sample below is purely fictious, but somewhat representative of the full dataset. I don't see an option to attach this as file and not able to send a link for OneDrive, Google Drive, Dropbox, etc. Hopefully you can work with this sample. The sorting on the "Code" column in the table is the issue.

 

DescriptionCodeInventory Qty.Cost
Gloves, Black - Small01128$3.99
Gloves, Black - Medium02340$4.99
Gloves, Black - Large03429$5.99
Gloves, Brown - Small7E1310$3.99
Gloves, Brown - Medium08511$4.99
Gloves, Brown - Large10754$5.99
Gloves, Blue - Small22923$3.99
Gloves, Blue - Medium27054$4.99
Gloves, Blue - Large36071$5.99
Gloves, Red - Small42094$3.99
Gloves, Red - Medium5015$4.99
Gloves, Red - Large62744$5.99
Gloves, White - Small90163$3.99
Gloves, White - Medium09G741$4.99
Gloves, White - Large0Z545$5.99
Mittens - Orange39Z55$2.99
Mittens - Red3G59$2.99
Mittens - Blue41N72$2.99
Mittens - Green55Q34$2.99
Mittens - Yellow6DZ45$2.99
Mittens - Black7FF16$2.99
Mittens - Purple82A112$2.99
Mittens - Pink98E33$2.99
Mittens - White99R4$2.99

 

@trs1111 

I pasted your sample data into an Excel sheet formatted as text, then sorted on the Code column:

HansVogelaar_0-1697655151832.png

Perhaps you can attach a sample workbook in a private message to me (click on my user picture)

best response confirmed by trs1111 (Brass Contributor)
Solution
Hi Hans,

As mentioned in my private message to you, I re-attempted the sort successfully on Excel for the web and another PC that is running a different build of Excel desktop. So it appears that the sort issue has something to do with the Excel desktop build on my other PC.

Thank you again for your time and expertise!
1 best response

Accepted Solutions
best response confirmed by trs1111 (Brass Contributor)
Solution
Hi Hans,

As mentioned in my private message to you, I re-attempted the sort successfully on Excel for the web and another PC that is running a different build of Excel desktop. So it appears that the sort issue has something to do with the Excel desktop build on my other PC.

Thank you again for your time and expertise!

View solution in original post