Andreamary
asked on
Developing and automating a custom alphanumeric sort in Excel
I need to be able to develop and automate a custom sort on an alphanumeric list. We will be sorting this type of data on a regular basis, hence the most automated approach would be best. I have attached a sample excel showing the Excel default sort in column A, and the correct custom sort I need in column B.
Thanks,
Andrea
Sample-alphanumeric-sort.xlsx
Thanks,
Andrea
Sample-alphanumeric-sort.xlsx
If MacroShadow is correct, to achieve that sort use a helper cell alongside the data with the following formula:
=LEN(A2)
Sort on the column contianing this formula and then on column A.
Thanks
Rob H
=LEN(A2)
Sort on the column contianing this formula and then on column A.
Thanks
Rob H
ASKER
I tried the above formula but it wasn't entirely successful...I've attached my results and highlighted the errors in red. They pertain to the "N" section, with those identified as "4's" in the helper column. To answer Macroshadow to the best of my abilities:
First sort — alpha, on first letter in string
Second sort — numeric, on all numbers within string, treating the number sequence within the string as a single number (it can be 1, 2, 3+ digits as per the example).
Third sort — alpha, on last letter in string
Thanks!
Andrea
Alphanumeric-HelperColumn.xlsx
First sort — alpha, on first letter in string
Second sort — numeric, on all numbers within string, treating the number sequence within the string as a single number (it can be 1, 2, 3+ digits as per the example).
Third sort — alpha, on last letter in string
Thanks!
Andrea
Alphanumeric-HelperColumn.xlsx
Followup: Where would the value "CUT16" be ordered? It does not have a single letter to start, but based on your rules above, I presume it should be at the top of the list. Are there any other designations like this that might need to be considered?
ASKER
Hi Glenn,
Thanks for noting the CUT exception...I'm not very familiar with the data (troubleshooting for a colleague) but you're right, CUT should be at the top of the list. And confirming that I did sort as per your example with the helper column.
Andrea
Thanks for noting the CUT exception...I'm not very familiar with the data (troubleshooting for a colleague) but you're right, CUT should be at the top of the list. And confirming that I did sort as per your example with the helper column.
Andrea
So, given that issue, would it be correct to re-state the sorting algorithm like so?
1) Sort on all alphabetic characters at the start of each string, ascending
2) Sort on all numeric characters that follow, ascending
3) Sort on any alphabetic characters at the end of the string (if they exist), ascending
1) Sort on all alphabetic characters at the start of each string, ascending
2) Sort on all numeric characters that follow, ascending
3) Sort on any alphabetic characters at the end of the string (if they exist), ascending
ASKER
Yes, a formula (or VBA) that does that should do the trick...
Thanks,
Andrea
Thanks,
Andrea
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Glenn,
Thanks very much. I've tested this out, and it appears that when I sort by Column B (SortString), then by column A, there are still issues with the numbers being out of order (3-digit numbers preceding 2-digit numbers as shown below). It appears, though, if I change the order in which I sort the columns, sorting Column A first, then Column B, it appears to work. Is there a reason it would work this way, and would it always work? I've attached my test SortString file.
N388A
N389A
N38F
N419A
N41D
N420A
AlphaNumeric-SortString-Test.xlsm
Thanks very much. I've tested this out, and it appears that when I sort by Column B (SortString), then by column A, there are still issues with the numbers being out of order (3-digit numbers preceding 2-digit numbers as shown below). It appears, though, if I change the order in which I sort the columns, sorting Column A first, then Column B, it appears to work. Is there a reason it would work this way, and would it always work? I've attached my test SortString file.
N388A
N389A
N38F
N419A
N41D
N420A
AlphaNumeric-SortString-Test.xlsm
ASKER
It works perfectly, Glenn...much appreciated! (Thanks for your patience in steering me in the right direction..I was manually sorting each column instead of using the SORT interface as shown in your screen capture.)
Cheers,
Andrea
Cheers,
Andrea
You're welcome. Wish I could have figured an "elegant" helper formula instead of a UDF, but glad that it works.
-Glenn
-Glenn
1. The data should be separated into subsets for each number of characters per word
2. Each subset should be sorted by itself.
3. Once the data is separated into subsets it actually is sorted already.