sort numbers with letters

[rjplante]

I have the macro below which is used to sort a block of numbers and some numbers with letters:

306
306A
306D
308

The request has now been made to have a preceding letter. I need to adjust the macro so that it sorts the numbers in the following order. The preceding number will always be an N.

306
306A
306D
N306
308

I do not know how to adjust this macro as I obtained the macro here from one of the wizards who know VBA way better than I do. I don't fully understand how it works. If anyone can help me adjsut this so that it sorts the numbers like I have listed above, that would be great.

Thanks for the help.

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 All").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
 
  Call FC_CLEAR
 
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

 

[Akuini]

If there is 306P, what should be the result:
this:
306
306A
306P
N306
308

or this:

306
306A
N306
306P
308

Edit:
Another question:
Are the number always 3 digit & the letter is only one letter?

 

[Rick Rothstein]

In addition to the question @Akuini asked (which you should answer), I would like to know...
1) Are the numbers always all three digits long?
2) Are the letters always one character long?
3) Could there be a letter in the front and at the back of the number within a single cell?
4) Since you are using xl365, would a formula solution be acceptable?

 

[rjplante]

If there is 306P, what should be the result:
this:
306
306A
306P
N306
308

or this:

306
306A
N306
306P
308

Edit:
Another question:
Are the number always 3 digit & the letter is only one letter?

The numbered portion of the string can range from three digits to as many as 5 digits.
The lettered characters after the numeric portion of the string will only be one characters long.
The lettered characters at the beginning will only be a letter N.

The first order of the sort is correct:
this:
306
306A
306P
N306
308

 

[Rick Rothstein]

Could the letter at the end of the number ever be an "N" ?

 

[rjplante]

Could the letter at the end of the number ever be an "N" ?

Yes the ending number could be an N.

 

[Akuini]

The numbered portion of the string can range from three digits to as many as 5 digits.

If there is 10000, what should be the result?
this:
10000
306
306A
306P
N306
308

or this:

306
306A
306P
N306
308
10000

 

[Rick Rothstein]

I believe this formula will produce the sorted values you are looking for (change the range at the beginning to the actual range containing your data)...

=LET(r,A1:A20,a,IF(CODE(RIGHT(r))>64,r,r&"x"),b,SORT(RIGHT("       "&IF(LEFT(a)="N",MID(a,2,9)&"|",a&" "),7)),c,SUBSTITUTE(b," ",""),SUBSTITUTE(SUBSTITUTE(IF(RIGHT(c)="|","N"&c,c),"x",""),"|",""))

 

[rjplante]

Rick,

That formula is a monster. I do not know where I would put it in the spreadsheet. I have a VBA user form that I use to capture the data I need for the fields. The VBA code when they select OK on the user form will place the values in the correct cells and then initiate the sort. I tried to use XL2BB but it said it would not work in protected view. An image of my table is shown below. The number of panels may vary depending on the system design. There are no formulas in the workbook, just the data entered from the userform1

My user form is shown below.

My full code that I use on the OK button of the user form is listed below.

NOTE: From the userform1 - Panel field is linked to cell CA1, the terminal Block field is linked to cell CC1, and the Current field in linked to cell DN1. CF1 contains the number of panels on the sheet. In the example above the value in CF1 would be 4. So the reference column and the Current column has to be factored into my cell references.


I hope this more complete explanation helps understand better what I am trying to accomplish.

Thanks for all the assistance. This one is a significant challenge for me. I am learning a lot so far.

Robert

UserForm1.Hide

'Notify user of no value.
Sheets("Block Tracking All").Unprotect Password:="dmt"

If Sheets("Block Tracking All").Range("CC1").Value = "" Or Sheets("Block Tracking All").Range("CC1").Value = "" Then

    MsgBox "There is no value entered into the terminal block." & vbCrLf & "Enter a value for both the Panel and the Terminal Block, or select the Cancel button.", vbOK + vbExclamation
    Sheets("Block Tracking All").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, Password:="dmt"
    Call New_Entry
    Exit Sub
    
End If

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

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

If LastRow < 5 Then
    LastRow = 4
End If

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

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

'   Paste value in correct column
    Range("A" & LastRow + 1).Offset(0, Sheets("Block Tracking All").Range("CA1").Value).Value = Sheets("Block Tracking All").Range("CC1").Value
    Range("A" & LastRow + 1).Offset(0, Sheets("Block Tracking All").Range("CF1").Value + 1).Value = Sheets("Block Tracking All").Range("DN1").Value
    
ElseIf MyRow > 0 Then
    Cells(MyRow.Row, Sheets("Block Tracking All").Range("CA1").Value + 1).Value = Sheets("Block Tracking All").Range("CC1").Value
    Cells(MyRow.Row, Sheets("Block Tracking All").Range("CF1").Value + 2).Value = Sheets("Block Tracking All").Range("DN1").Value

End If

If Sheets("Block Tracking All").Range("A6").Value <> "" Then

    Call SortNumbersWithLetters

End If

Sheets("Block Tracking All").Range("A5").Select

Sheets("Block Tracking All").Range("CX1").Value = "YES"
Sheets("Block Tracking All").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, Password:="dmt"

End Sub

 

[Rick Rothstein]

You asked for a sorted list, correct? Is that sorted list what you show in the "Reference Row" column? If so, and given what you showed as your layout, where on the worksheet did you want the outputted sorted list to go?