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

 

[rjplante]

I want the sorted list based on the reference column (yes). My macro will add a new line to the end of the active list and then execute the sort macro to organize the data based on the parameters I have listed above and shown here below. I do not want the data moved anywhere, just sorted in place. Sorry for my confusion.

306
306A
306P
N306
308
10000

 

[Akuini]

I don't quite understand what you're trying to accomplish based on your description in post #9, but here is a macro to sort the data according to your requirements mentioned in post #1:

Sub rjplante_1()
'rjplante_1 - sort numbers with letters
Dim c As Range
Dim vb
Dim x As String
Dim i As Long, n As Long

Set c = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Resize(, 2) 'data start at A2, change to suit
vb = c

For i = 1 To UBound(vb, 1)
    x = vb(i, 1)
    If IsNumeric(x) Then
        x = x & "#"
        n = 8 - Len(x)
        vb(i, 1) = String(n, "0") & x
        vb(i, 2) = n
    Else
        If Left(x, 1) = "N" Then
            x = Mid(x, 2, 100) & "ZZ"
            n = 9 - Len(x)
            vb(i, 1) = String(n, "0") & x
            vb(i, 2) = n
        Else
            n = 8 - Len(x)
            vb(i, 1) = String(n, "0") & x
            vb(i, 2) = n
        End If
    End If
Next


vb = WorksheetFunction.Sort(vb) '2d array

For i = 1 To UBound(vb, 1)
    vb(i, 1) = Replace(vb(i, 1), "#", "")
Next

    For i = 1 To UBound(vb, 1)
        If Not IsNumeric(vb(i, 1)) Then vb(i, 1) = Mid(vb(i, 1), vb(i, 2) + 1, 100)
        If InStr(vb(i, 1), "ZZ") Then vb(i, 1) = "N" & Replace(vb(i, 1), "ZZ", "")
    Next
    
    c.Columns(1).Value = Application.Index(vb, , 1)

End Sub

Example:
Before:

rjplante_1 - sort numbers with letters.xlsm
	A
1	Data
2	306A
3	306P
4	400
5	306
6	N306
7	3007
8	3007A
9	306Z
10	N3067
11	1000
12	1000A
Sheet1

After:

rjplante_1 - sort numbers with letters.xlsm
	A
1	Data
2	306
3	306A
4	306P
5	306Z
6	N306
7	400
8	1000
9	1000A
10	3007
11	3007A
12	N3067
Sheet1

 

[rjplante]

Akuini - Thanks for the macro update. Once I changed the initial line to start at A5 on my worksheet, it works, but it doesn't grab the full table. This number is variable and is linked to a value in CF1 (the total number of panels) For the image in post 9, there are four panels, plus the reference row, and the Current row at the end, so the total number of columns is the value in CF1 = 4 + 2 for a total of 6 columns. Which line to I need to modify to capture the entire table for the sorting process?

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[rjplante]

I have modified the line below and I believe it is now capturing the entire table.
Original
Set c = Range("A5", Cells(Rows.Count, "A").End(xlUp)).Resize(, 5)

Changed to
Set c = Range("A5", Cells(Rows.Count, "A").End(xlUp)).Resize(, Range("CF1").Value + 2)

I also noticed a second issue. If I have a number on the list (306 from your example) and it is using a DC power circuit, and then I add a 306 with an AC powered circuit, it will overwrite the DC value with the AC value instead of adding a new line for the 306 component with the AC value.

 

[Akuini]

Which line to I need to modify to capture the entire table for the sorting process?

My code is to sort 1 column only, it's because the sorting part happens in an array instead of sheet.
If you want to include other columns in the sorting then we need different code.
Could you post a sample data using XL2BB tools?

I also noticed a second issue. If I have a number on the list (306 from your example) and it is using a DC power circuit, and then I add a 306 with an AC powered circuit, it will overwrite the DC value with the AC value instead of adding a new line for the 306 component with the AC value.

I don't understand your description, you need to explain it with a sample data.

 

[rjplante]

I tried to add XL2BB to my spreadsheet and capture the range of cells, but it doesn't allow me to add it to a protected sheet. I turned off all protected sheets and it still would not allow me to add it. The best I can do is send a screen image of the area I am referencing.

In the image above, 306 appears in row 1 with DC as the current. It also has termination points in panel 1 and 3. The is also a 306 in panel 2 that is an AC current. If a 306 termination was added to panel 1 with an AC current, it should be on the same row as the 306 in panel 2. Because all of the data across the columns are linked, they need to be sorted together.

 

[Akuini]

Is your data in an actual Table, not just a range?
If so, what's the table name?
I ask this because we will need a temporary helper column.

I tried to add XL2BB to my spreadsheet and capture the range of cells, but it doesn't allow me to add it to a protected sheet.

Are you saying you can't install XL2BB add-in? or you have installed it but it doesn't work ?

 

[rjplante]

It is not a defined table, just a range.

I have the XL2BB add-in installed, Excel will not allow me to turn it on.

 

[Akuini]

It is not a defined table, just a range.

Adjust helper column in this part:
HC = "G" 'temporary helper column, change to suit, must be a blank column on the right of data set

Sub rjplante_2()
'rjplante_1 - sort numbers with letters
Dim c As Range
Dim va, vb
Dim x As String
Dim HC As String
Dim i As Long, m As Long, n As Long

Range("i1:n13").Copy Range("A1")

HC = "G"  'temporary helper column, change to suit, must be a blank column on the right of data set

Set c = Range("A2", Cells(Rows.Count, "A").End(xlUp)) 'data start at A2, change to suit
vb = c
ReDim va(1 To UBound(vb, 1), 1 To 1)

For i = 1 To UBound(vb, 1)
    x = vb(i, 1)
    If IsNumeric(x) Then
        x = x & "#"
        n = 8 - Len(x)
        vb(i, 1) = String(n, "0") & x
        va(i, 1) = n
    Else
        If Left(x, 1) = "N" Then
            x = Mid(x, 2, 100) & "ZZ"
            n = 9 - Len(x)
            vb(i, 1) = String(n, "0") & x
            va(i, 1) = n
        Else
            n = 8 - Len(x)
            vb(i, 1) = String(n, "0") & x
            va(i, 1) = n
        End If
    End If
Next

Application.ScreenUpdating = False
c = vb
m = Columns(HC).Column - c.Column + 1
c.Offset(, m - 1) = va

With c
    .Resize(, m).Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
    DoEvents
    vb = .Value
    va = c.Offset(, m - 1).Value
    For i = 1 To UBound(vb, 1)
        vb(i, 1) = Mid(vb(i, 1), va(i, 1) + 1, 100)
        vb(i, 1) = Replace(vb(i, 1), "#", "")
        If InStr(vb(i, 1), "ZZ") Then vb(i, 1) = "N" & Replace(vb(i, 1), "ZZ", "")
    Next
    .Value = vb
End With

Columns(HC).ClearContents
Application.ScreenUpdating = True

End Sub

I have the XL2BB add-in installed, Excel will not allow me to turn it on.

Probably Trusted Location issue:
trusting-source-for-xl2bb