Sort numbers with letters at the end

[rjplante]

I have a column of data listed below. I want to be able to sort the numbers first and then the numbers based on the letters.

Original Data
907
1213
1508
907A
1215
1305
1508A
1691
1710
1508B
1711
1306

Sorted Data (ideal)
907
907A
1213
1215
1305
1306
1508
1508A
1508B
1691
1710
1711

I have gone into the advanced options and added a custom list that reads from A-Z, but that did not help in the sorting. How do I get this to sort correctly, using the numbers and then the letters (if they are present) to build the complete correctly sorted list? From other sources I have read I may need a helper cell, or reference column to manage the parsing of the data. I will be creating a macro which generates a user form and then adds the new entered number to the list column and then sorts the column which would place the number in its correct position.

 

[JamesCanale]

Assumptions - you have excel365. The letters are always just one letter on the end of what would otherwise be a number. There is room for helper columns.

MrExcelPlayground2.xlsm
	P	Q	R	S	T
1	907	907	907	1	907
2	1213	1213	1213	3	907A
3	1508	1215	1215	4	1213
4	907A	1305	1305	5	1215
5	1215	1306	1306	6	1305
6	1305	1508	1508	7	1306
7	1508A	1691	1691	10	1508
8	1691	1710	1710	11	1508A
9	1710	1711	1711	12	1508B
10	1508B	1508A	1508	7	1691
11	1711	1508B	1508	7	1710
12	1306	907A	907	1	1711
Sheet35

Cell Formulas
Range	Formula
Q1:Q12	Q1	=SORT(P1:P12)
R1:R12	R1	=IF(ISTEXT(Q1:Q12),VALUE(LEFT(Q1:Q12,LEN(Q1:Q12)-1)),Q1:Q12)
T1:T12	T1	=SORTBY(Q1#,S1:S12,1)
S1:S12	S1	=RANK.EQ(R1,R$1#,1)
Dynamic array formulas.

 

[Joe4]

Here is another way, using one helper column, assuming that all your numeric portions are exactly 3 or 4 numbers long.

Let's say that your values are in column A, and you first value is in cell A1.
Then put this formula in cell B1 and copy down for all values in column A:

=IF(LEN(A1)<4,TEXT(A1,"0000"),IF(AND(LEN(A1)=4,ISNUMBER(RIGHT(A1,1)+0)=FALSE),"0" & A1,TEXT(A1,"0000")))

Then, if you sort by this "helper" column, your values will be in the order that you want.

 

[DanteAmor]

I will be creating a macro which generates a user form and then adds the new entered number to the list column

After adding the data, run the following macro SortNumbersWithLetters to sort the data in the same column.

Copy all the code in the same module:

Option Explicit

Dim numbers As New Collection

Sub SortNumbersWithLetters()
  Dim c As Range
  Dim a As Variant, n As Variant, m As Variant, x As Variant
 
  Set numbers = Nothing
  a = Range("A2", Range("A" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    n = Val(a(i, 1))
    m = Mid(a(i, 1), Len(n) + 1)
    addnum Format(n, "000000000") & m
  Next
 
  i = 1
  For Each x In numbers
    n = Val(x)
    m = Mid(x, 10)
    b(i, 1) = Val(n) & m
    i = i + 1
  Next
  Range("A2").Resize(UBound(b)).Value = b
End Sub

Sub addnum(c)
  Dim i As Long
  For i = 1 To numbers.Count
    Select Case StrComp(numbers(i), c, vbTextCompare)
    Case 0, 1: numbers.Add c, Before:=i: Exit Sub
    End Select
  Next
  numbers.Add c 'add to end
End Sub

 

[Snakehips]

Given @DanteAmor 's vba solution, this is probably of little value to the OP but, I post it regardless.

Not restricted to just 3 or 4 numbers and using only one Helper column.
It does however assume only a single Alpha character suffix.
Sort on Helper column B

MRXLMAY21.xlsm
	A	B
1	Original Data	Helper
2	907	907
3	907A	907.02
4	1213	1213
5	1215	1215
6	1305	1305
7	1306	1306
8	1508	1508
9	1508A	1508.02
10	1508B	1508.04
11	1508Z	1508.52
12	1691	1691
13	1710	1710
14	1711	1711
15	15082	15082
16	15082C	15082.06
check list

Cell Formulas
Range	Formula
B2:B16	B2	=IF(ISNUMBER(1*A2),A2,MID(A2,1,LEN(A2)-1)+((CODE(RIGHT(A2,1))-64)*0.02))

 

[rjplante]

After adding the data, run the following macro SortNumbersWithLetters to sort the data in the same column.

Copy all the code in the same module:

Option Explicit

Dim numbers As New Collection

Sub SortNumbersWithLetters()
  Dim c As Range
  Dim a As Variant, n As Variant, m As Variant, x As Variant
 
  Set numbers = Nothing
  a = Range("A2", Range("A" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    n = Val(a(i, 1))
    m = Mid(a(i, 1), Len(n) + 1)
    addnum Format(n, "000000000") & m
  Next
 
  i = 1
  For Each x In numbers
    n = Val(x)
    m = Mid(x, 10)
    b(i, 1) = Val(n) & m
    i = i + 1
  Next
  Range("A2").Resize(UBound(b)).Value = b
End Sub

Sub addnum(c)
  Dim i As Long
  For i = 1 To numbers.Count
    Select Case StrComp(numbers(i), c, vbTextCompare)
    Case 0, 1: numbers.Add c, Before:=i: Exit Sub
    End Select
  Next
  numbers.Add c 'add to end
End Sub

Dante,

The above worked great after I declared "i" as an integer.

My next hurdle is that I have column A defined as my reference Column. Then the user will enter the desired number of columns to create for the table. That number resides in cell CC1. How can I expand my sort range to include column A plus the number of columns listed in cell CC1? So if the user enters 8 there would be 9 total columns to sort (column A + 8 more).

 

[DanteAmor]

The above worked great after I declared "i" as an integer.

Sorry for that, I forgot to declare i as long.

the user enters 8 there would be 9 total columns to sort (column A + 8 more).

I do not know if I understood correctly, if the user puts 8 then we must order from A to I?

 

[DanteAmor]

Another question.
In column A do you have unique numbers or are there repeating values?

 

[DanteAmor]

If all of the above is correct, then try the following:

Sub SortNumbersWithLetters()
  Dim c As Range
  Dim a As Variant, n As Variant, m As Variant, x As Variant
  Dim i As Long, j As Long, k As Long
  Dim col As Long
  
  Set numbers = Nothing
  col = Range("CC1").Value + 1
  
  a = Range("A2", Cells(Range("A" & Rows.Count).End(3).Row, col)).Value
  ReDim b(1 To UBound(a), 1 To col)
  For i = 1 To UBound(a)
    n = Val(a(i, 1))
    m = Mid(a(i, 1), Len(n) + 1)
    addnum Format(n, "000000000") & m
  Next
  
  i = 1
  For Each x In numbers
    n = Val(x)
    m = Mid(x, 10)
    b(i, 1) = Val(n) & m
    For j = 1 To UBound(a)
      If "" & a(j, 1) = b(i, 1) Then
        For k = 2 To UBound(a, 2)
          b(i, k) = a(j, k)
        Next
        Exit For
      End If
    Next
    i = i + 1
  Next
  Range("A2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

Sub addnum(c)
  Dim ii As Long
  For ii = 1 To numbers.Count
    Select Case StrComp(numbers(ii), c, vbTextCompare)
    Case 0, 1
      numbers.Add c, Before:=ii
      Exit Sub
    End Select
  Next
  numbers.Add c 'add to end
End Sub

 

[rjplante]

Sorry for that, I forgot to declare i as long.



I do not know if I understood correctly, if the user puts 8 then we must order from A to I?

Column A will have only unique values, no repeating values in column A. When the file is initially opened, a userform is launched and asks the user to enter the number of panels to configure (8 for example). Column A is already labeled as Reference Column. The added columns are labeled as Panel 1, Panel 2 ... Panel 8. When the user is entering data they are given a userform and they enter the block number (CC1) and then the panel number (CA1). The first part of macro searches column A for the block number (CC1) if one is not found, it adds it to the bottom of column A and then also adds it to the column found in CA1. If the block is found in column A, it will just add the block to the panel in CA1.

If all of the above is correct, then try the following:

Sub SortNumbersWithLetters()
  Dim c As Range
  Dim a As Variant, n As Variant, m As Variant, x As Variant
  Dim i As Long, j As Long, k As Long
  Dim col As Long
 
  Set numbers = Nothing
  col = Range("CC1").Value + 1
 
  a = Range("A2", Cells(Range("A" & Rows.Count).End(3).Row, col)).Value
  ReDim b(1 To UBound(a), 1 To col)
  For i = 1 To UBound(a)
    n = Val(a(i, 1))
    m = Mid(a(i, 1), Len(n) + 1)
    addnum Format(n, "000000000") & m
  Next
 
  i = 1
  For Each x In numbers
    n = Val(x)
    m = Mid(x, 10)
    b(i, 1) = Val(n) & m
    For j = 1 To UBound(a)
      If "" & a(j, 1) = b(i, 1) Then
        For k = 2 To UBound(a, 2)
          b(i, k) = a(j, k)
        Next
        Exit For
      End If
    Next
    i = i + 1
  Next
  Range("A2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

Sub addnum(c)
  Dim ii As Long
  For ii = 1 To numbers.Count
    Select Case StrComp(numbers(ii), c, vbTextCompare)
    Case 0, 1
      numbers.Add c, Before:=ii
      Exit Sub
    End Select
  Next
  numbers.Add c 'add to end
End Sub

Everything is working great! Thanks for all the help.