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.

 

[Radoslaw Poprawski]

ur not specifying ur office.
Here are 2 options with a formula / helper column.

Book5 (version 1).xlsb
	A	B	C	D
1	Original Data	Sorted Data (ideal)	Helper	With LET
2	907	1	907	907
3	907A	2	907	907
4	1213	3	1213	1213
5	1215	4	1215	1215
6	1305	5	1305	1305
7	1306	6	1306	1306
8	1508	7	1508	1508
9	1508A	8	1508	1508
10	1508B	9	1508	1508
11	1691	10	1691	1691
12	1710	11	1710	1710
13	1711	12	1711	1711
Sheet2

Cell Formulas
Range	Formula
C2:C13	C2	=--(CONCAT(FILTER(ISNUMBER(MID(A2,SEQUENCE(,LEN(A2),1,1),1)+0)*MID(A2,SEQUENCE(,LEN(A2),1,1),1),ISNUMBER(MID(A2,SEQUENCE(,LEN(A2),1,1),1)+0))))
D2:D13	D2	=LET( Chars,MID(A2,SEQUENCE(,LEN(A2),1,1),1), NumberCheck,ISNUMBER(MID(A2,SEQUENCE(,LEN(A2),1,1),1)+0), --(CONCAT(FILTER(NumberCheck*Chars,NumberCheck))))

 

[DanteAmor]

Everything is working great! Thanks for all the help.

I'm glad to help you. Thanks for the feedback.

 

[rjplante]

Dante,

I ran into an issue with my spreadsheet with this macro. The table has a cell with a five digit number 21003. This appeared in Column A, my reference column, as well as in column B, which is used for Panel 1. The user then wanted to add a new block number in the column for Panel 2 (col C) and the block number was 1003. When they selected OK on my user form, it added it to column C for panel 2 correctly but it added it on the row for 21003 instead of creating a new row for 1003. How to I require the macro to find an exact match to the block being added to prevent the new block being added to a row that contains the number but is not an exact match? From how I understand your original macro to work, it should be able to handle nine digit numbers along with letters.

The macro I am currently running on the spreadsheet is listed below.


MACRO CODE:

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
  Dim i As Long, j As Long, k As Long
  Dim col As Long
 
  Set numbers = Nothing
  col = Sheets("Block Tracking Multiple").Range("CF1").Value + 1
 
  a = Range("A5", 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("A5").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

 

[Rick Rothstein]

I wrote the code below after reading the original question and not realizing this was an "old" question that was revived. The code below answers your original question and also handles values that are not all 4-digits long. I am unclear about your "panels" so I don't think this code addresses them. If you can adapt the code, fine, if not, please clarify what is supposed to happen with the "panels" (are they being sorted? sorted individually without regard to other columns? sorted as one big bunch? something else?).

Sub SortNumbersWithLetters()
  With Range("A2", Cells(Rows.Count, "A").End(xlUp))
    .NumberFormat = "@"
    .Value = Evaluate(Replace("TEXT(IF(ISNUMBER(-RIGHT(@)),@,SUBSTITUTE(@,RIGHT(@),"".""&CODE(RIGHT(@)))),""00000000.00"")", "@", .Address))
    .Sort Columns("A"), xlAscending
    .NumberFormat = "General"
    .Value = Evaluate(Replace("IF(ISNUMBER(FIND("".00"",@)),0+@,SUBSTITUTE(0+@,RIGHT(@,3),CHAR(0+RIGHT(@,2))))", "@", .Address))
  End With
End Sub

 

[DanteAmor]

How to I require the macro to find an exact match to the block being added to prevent the new block being added to a row that contains the number but is not an exact match?

I am not understanding well. The problem you have is with the way to sort the data or with the way your userform searches for data?

 

[rjplante]

I have copied a portion of the spreadsheet and included it below so that you can see hot the sheet is laid out. I will include additional portion of my code below as well.


Spreadsheet layout:

Panel Tracking Sheet - 3145.xlsm
	A	B	C	D	E	F	G	H	I
2	Cross Cabinet Connections
3	Reference Row	Panel 1	Panel 2	Panel 3	Panel 4	Panel 5	Panel 6	Panel 7	Panel 8
5	907	907
6	907A	907A
7	1203	1203
8	1204B	1204B
9	1205	1205
10	1206	1206
11	1209	1209
12	2122	2122
13	4402	4402
14	12008A	12008A
15	14003	14003
16	14004	14004
17	14006	14006
18	14009	14009
19	14015	14015
20	20410	20410
21	20413	20413
22	21003	21003	1003
23	21004	21004
24	21008A	21008A
25	21009A	21009A
26	21010	21010
Block Tracking Multiple

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:AZ3	Cell	does not contain a blank value	text	NO
D2:J2,L2:AZ2	Expression	=D3<>""	text	NO

Reference cells to capture data from Userform1:

Panel Tracking Sheet - 3145.xlsm
	BZ	CA	CB	CC	CD	CE	CF
1	Panel	2	Block	1003		Total Panels	14
Block Tracking Multiple

Userform1 Macro "OK" button code:

The MyRow find line of code is most likely where my issue lies. I do not know how to require an exact match on this line of code. I believe that a correction on this line would solve the problem.

Private Sub CommandButton2_Click()

UserForm1.Hide

Dim LastRow As Long
Dim MyRow As Range
Dim x As Integer


LastRow = Cells(Rows.Count, 1).End(xlUp).Row

With Sheets("Block Tracking Multiple")
    Set MyRow = .Range("A5:A" & LastRow).Find(What:=Range("CC1").Value, LookIn:=xlValues)
End With

If MyRow Is Nothing Then
    Range("A" & LastRow + 1).Value = Range("CC1").Value

'   Paste value in correct column
    Range("A" & LastRow + 1).Offset(0, Range("CA1").Value).Value = Range("CC1").Value
  
ElseIf MyRow > 0 Then
    Cells(MyRow.Row, Range("CA1").Value + 1).Value = Range("CC1").Value
  
End If

Call SortNumbersWithLetters

Call ADD_Border

End Sub

 

[DanteAmor]

Change this line:

Set MyRow = .Range("A5:A" & LastRow).Find(What:=Range("CC1").Value, LookIn:=xlValues)

For this line:

Set MyRow = .Range("A5:A" & LastRow).Find(What:=Range("CC1").Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)

Note:
MatchCase:=True is case sensitive. If it doesn't matter if it is uppercase or lowercase then change to False

 

[rjplante]

Thank you all for you time. After some additional digging on my part, I found my error in the code. I needed to add "lookat:=xlwhole" to my find line to force the exact match. Once I did that, I am back in business. I appreciate the help and support of this community as I find you all to be massive excel wizards and hope to be a resource half as good as you someday.

 

[rjplante]

Dante, I didn't even see your response until my browser refreshed. I add the "MatchCase:=True" component to the line to ensure I am forcing the exact match. Thanks again.

 

[DanteAmor]

Again with pleasure. Thanks for the feedback