Increase letter and number sequence in order

[mc612]

I am creating a SKU system for a huge catalogue of items that will grow and grow over time, I'm looking to future proof with a SKU that can create over a billion variations so I will never run out and is easy to understand

I'm looking at the following:

AA0A0AA
AA0A0AB
AA0A0AC

up to:

ZZ9Z9ZX
ZZ9Z9ZY
ZZ9Z9ZZ

I understand I can only go to a little over 1 million at a time, therefore is there a formula to increment the start point by 1, so if I start with AA0A0AA it will increment to AA0A0AB, equally if I started randomly with say FV3D8TH would increment to FV3D8TI?

 

[DanteAmor]

Hi @mc612 and welcome to MrExcel board!
I suggest you run a macro.

On a sheet, in cell A2 put the initial SKU, it can be AA0A0AA or FV3D8TH.

In cell B2 write the growth number you want, it can be 10, 1000 or a million (this could take a while to generate).

Example:

Dante Amor
	A	B
1	SKU	GROW
2	FV3D8TH	10
3
4
5
6
7
8
9
10
11
12
Hoja1

After run the macro:

Dante Amor
	A	B
1	SKU	GROW
2	FV3D8TH	10
3	FV3D8TI
4	FV3D8TJ
5	FV3D8TK
6	FV3D8TL
7	FV3D8TM
8	FV3D8TN
9	FV3D8TO
10	FV3D8TP
11	FV3D8TQ
12	FV3D8TR
Hoja1

Put the following macro in a module:

Sub Increase_letter_and_number_v1()
  Dim StartCell As String, sku As String, itm As String
  Dim i As Long, nGrow As Long, n As Long, aItm As Long
  Dim cell As Range
  Dim a As Variant
  
  StartCell = "A2"
  nGrow = Range("B2")
  
  Set cell = Range(StartCell)
  Range(Cells(cell.Row + 1, cell.Column), Cells(Rows.Count, cell.Column)).ClearContents
  
  sku = cell.Value
  n = 7
  ReDim a(1 To nGrow, 1 To 1)
  
  For i = 1 To nGrow
    Do While True
      Select Case n
        Case 7                            'letter
          itm = Mid(sku, n, 1)
          aItm = Asc(itm) + 1
          If aItm = 91 Then
            n = 6
            sku = Left(sku, 6) & "A"
          Else
            n = 7
            sku = Left(sku, 6) & Chr(aItm)
            Exit Do
          End If
        Case 6                            'letter
          itm = Mid(sku, n, 1)
          aItm = Asc(itm) + 1
          If aItm = 91 Then
            n = 5
            sku = Left(sku, 5) & "A" & Right(sku, 1)
          Else
            sku = Left(sku, 5) & Chr(aItm) & Right(sku, 1)
            n = 7
            Exit Do
          End If
        Case 5                            'number
          itm = Mid(sku, n, 1)
          aItm = itm + 1
          If aItm = 10 Then
            n = 4
            sku = Left(sku, 4) & "0" & Right(sku, 2)
          Else
            sku = Left(sku, 4) & aItm & Right(sku, 2)
            n = 7
            Exit Do
          End If
        Case 4                            'letter
          itm = Mid(sku, n, 1)
          aItm = Asc(itm) + 1
          If aItm = 91 Then
            n = 3
            sku = Left(sku, 3) & "A" & Right(sku, 3)
          Else
            sku = Left(sku, 3) & Chr(aItm) & Right(sku, 3)
            n = 7
            Exit Do
          End If
        Case 3                            'number
          itm = Mid(sku, n, 1)
          aItm = itm + 1
          If aItm = 10 Then
            n = 2
            sku = Left(sku, 2) & "0" & Right(sku, 4)
          Else
            sku = Left(sku, 2) & aItm & Right(sku, 4)
            n = 7
            Exit Do
          End If
        Case 2                            'letter
          itm = Mid(sku, n, 1)
          aItm = Asc(itm) + 1
          If aItm = 91 Then
            n = 1
            sku = Left(sku, 1) & "A" & Right(sku, 5)
          Else
            sku = Left(sku, 1) & Chr(aItm) & Right(sku, 5)
            n = 7
            Exit Do
          End If
        Case 1                            'letter
          itm = Mid(sku, n, 1)
          aItm = Asc(itm) + 1
          If aItm = 91 Then
            n = 0
            Exit Do
          Else
            sku = Chr(aItm) & Right(sku, 6)
            n = 7
            Exit Do
          End If
      End Select
    Loop
    
    If n = 0 Then Exit For
    a(i, 1) = sku
  Next
  
  cell.Offset(1).Resize(UBound(a)).Value = a
End Sub

INSERT A MACRO

Press Alt-F11 to open the VBA editor. From the menu select Insert > Module. On the sheet that opens, paste the code previous.​

Close the editor (press Alt-Q). From Excel, press Alt-F8 to open the macro selector, and select Increase_letter_and_number_v1 and press Run.​

Make sure you save your file as an "Excel Macro-Enabled Workbook.​

 

[DanteAmor]

I understand I can only go to a little over 1 million at a time
...
AA0A0AA
...
ZZ9Z9ZZ

In fact from the first combination to the last combination, the number of total combinations is 1,188,137,600 That is, more than one thousand one hundred million (billion)

 

[mc612]

I'm looking to future proof with a SKU that can create over a billion variations so I will never run out

Like I said, future proofing! We sell preowned clothing with every item having a new, unique SKU, so need a system that will pretty much never run out

 

[mc612]

Thanks for the macro code, I'll give that a go