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
AndreamaryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
Correct me if my observation is wrong. This is the sort you need:
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.
0
Rob HensonFinance AnalystCommented:
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
0
AndreamaryAuthor Commented:
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
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Glenn RayExcel VBA DeveloperCommented:
No points..just clarifying how you should sort using Rob's helper column:
sort order
-Glenn
0
Glenn RayExcel VBA DeveloperCommented:
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?
0
AndreamaryAuthor Commented:
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
0
Glenn RayExcel VBA DeveloperCommented:
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
0
AndreamaryAuthor Commented:
Yes, a formula (or VBA) that does that should do the trick...

Thanks,
Andrea
0
Glenn RayExcel VBA DeveloperCommented:
As I was writing a UDF (user-defined function) that would help supply a new sorting string, I noticed that there were no instances where there was more than one letter suffix for any given set of letter-number combinations.  For example:
N104A
N104D

N300A
N300C

Put another way, instead of the designations being constructed like this:
AlphaCode1 - NumericCode (- AlphaCode2)   <---last value optional
it's more like:
AlphaCode1 - AlphanumericCode

Ex:
N-300-A
N-300A

meaning that the alphanumeric group is mutually-exclusive.

Long story short, I wanted to make sure that this would work if the first case was true, so I added some extra test values (in green) to ensure the UDF would work.  The attached workbook contains this function:
Function SortString(strInput As String)
    Dim x As Integer
    Dim strNum As String
    Dim boolBreak As Boolean
    For x = 1 To Len(strInput)
        If IsNumeric(Mid(strInput, x, 1)) Then
            boolBreak = True
            Do
                strNum = strNum & Mid(strInput, x, 1)
                x = x + 1
            Loop Until IsNumeric(Mid(strInput, x, 1)) = False
            SortString = SortString & Format(Val(strNum), "000000")
        Else
            SortString = SortString & Mid(strInput, x, 1)
        End If
    Next x
End Function

Open in new window


You would sort on the helper column with the function (col. B), then the Designation column to ensure that extra letter combinations are picked up correctly.

Regards,
-Glenn
EE-Alphanumeric-HelperColumn.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndreamaryAuthor Commented:
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
0
Glenn RayExcel VBA DeveloperCommented:
When you do the sort, it should show the column names and you should select as follows:
correct sorting order
This is the only way it should work.

-Glenn
0
AndreamaryAuthor Commented:
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
0
Glenn RayExcel VBA DeveloperCommented:
You're welcome.  Wish I could have figured an "elegant" helper formula instead of a UDF, but glad that it works.

-Glenn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.