CODEWITHSUNDEEP
vbaexcelsortingalphanumeric
MaxK
MaxK

Reputation: 11

Alphanumeric sorting in Excel using VBA

I use create Bills Of Materials in Excel before importing into another program and I'm trying to sort these lists by Reference Designation which consist of prefix page number 1-200, followed by device type A-ZZ, followed by device number (ID) on that page 1-99, followed by letter which represents part of device A-Z (if that device consists of multiple parts).
Here are examples of RefDes:

1Q1
1S6
1S7
1T1
1VENT
1X1
1X2
1Y1
1Z1-A
1Z1-B
2A1-A
2A1-B
2A1-C
22M1
2QF1
2RB1
2Z1-A
2Z1-B
13A1-A
13A1-B
13A1-C
3A2-A
3A2-B
3A2-C

I want it sorted first by Device type A-ZZ, then by device ID, then device part, then by page.

1A1A
....
1A1Z
1A2A
....
1A2Z
2A1A
....
2A1Z
....
200A99Z
1B1A
....
200ZZ99Z

So that the list above is sorted like this:

2A1-A
2A1-B
2A1-C
3A1-A
3A1-B
3A1-C
13A1-A
13A1-B
13A1-C
22M1
1Q1
2QF1
2RB1
1S6
1S7
1T1
1VENT
1X1
1X2
1Z1-A
1Z1-B
2Z1-A
2Z1-B

So far I have been able to sort by page then by device type and even then it sorts wrong: 1A-1ZZ, then 1xA-1xZZ, then 2A-2ZZ, then 2xA-2xZZ, etc... I can get rid of the dash (-) when I build the BOM.

Upvotes: 1

Views: 2255

Answers (1)

TheGuyOverThere
TheGuyOverThere

Reputation: 130

I don't know if this is the most efficient way of doing it, but based on how your BOMs are constructed, it might be easier to split out the data into temporary columns, then sort it, then delete the columns.

Your sorting requirements and order seem too complex to do it any other way that I know of.

Sub SortBOMS()
Dim workingRange As Range
Dim workingCell As Range
Dim pageNumber As String
Dim deviceType As String
Dim deviceID As String
Dim devicePart As String
Dim i As Integer

Application.ScreenUpdating = False

'Obtains the full list. (Assumes you have no data after the 1millionth row)
Set workingRange = Range("A1:A" & Range("A1000000").End(xlUp).Row)

For Each workingCell In workingRange.Cells

    'Builds the page number
    pageNumber = ""
    For i = 1 To 3  'Used 3 since 200 is 3 characters
        If IsNumeric(Mid(workingCell.Text, i, 1)) Then
            pageNumber = pageNumber & Mid(workingCell.Text, i, 1)
        Else          
            Exit For    'Exits as soon as encounters a letter
        End If
    Next i
    'Writes the value after converting it to an integer
    Range("B" & workingCell.Row).Value = CInt(pageNumber)

    'Builds the device type
    deviceType = ""
    For i = 1 To 2  'Used 2 since ZZ is 2 characters
        If Not (IsNumeric(Mid(Split(workingCell.Text, pageNumber)(1), i, 1))) Then
            deviceType = deviceType & Mid(Split(workingCell.Text, pageNumber)(1), i, 1)
        Else
            Exit For    'Exits as soon as encounters a number
        End If
    Next i
    'Writes the value
    Range("C" & workingCell.Row).Value = deviceType

    'Builds the device ID
    deviceID = ""
    For i = 1 To 2  'Used 2 since 99 is 2 characters
        If IsNumeric(Mid(Split(workingCell.Text, pageNumber & deviceType)(1), i, 1)) Then
            deviceID = deviceID & Mid(Split(workingCell.Text, pageNumber & deviceType)(1), i, 1)
        Else    
            Exit For    'Exits as soon as encounters a letter or a dash
        End If
    Next i
    'Writes the value after converting it to an integer
    On Error Resume Next
    Range("D" & workingCell.Row).Value = CInt(deviceID)
    On Error GoTo 0

    'Builds the device part
    devicePart = ""
    If InStr(1, workingCell.Text, "-", vbTextCompare) > 0 Then
        devicePart = Split(workingCell.Text, "-")(1)
    End If
    'Writes the value
    Range("E" & workingCell.Row).Value = devicePart
Next workingCell

'Clean up
Application.ScreenUpdating = True
Set workingRange = Nothing
Set workingCell = Nothing
End Sub

After this runs, you should be able to sort the way you want to. Also, to get the results you want sort in this order, not the order you mentioned in the post:

  1. Device Type
  2. Page Number
  3. Device ID
  4. Device Part

Upvotes: 2

Related Questions