How can I generate a list of codes in Excel based on criteria?

[bluesquared]

​

I'm trying to make a list of codes based on a format of 001 A 001. They should be 001 to 062, A to E, 001 to 012. I need all combinations, so they would be:


001 A 001
001 A 002 to
001 A 012, then up to 001 E 012

Then 001 B 001 to 012, and so on until 001 E 001 to 012
Then 002, A to E, 001 to 012

That's 3,720 combinations. I've tried making lookups to make a count within each of the three columns and generating +1 on the row above, I've tried IF statements to try and work it out, but I'm getting nowhere.

 

[jmacleary]

Hi there welcome to the forum. This piece of code will generate all your combinations in column A, starting at A1:

Option Explicit

Sub CodeList()
    Dim Middle(5) As String
    Dim MidCount As Integer
    Dim RowCount As Integer
    Dim LeftBit As Integer
    Dim RightBit As Integer
    Application.ScreenUpdating = False
    Middle(1) = " A "
    Middle(2) = " B "
    Middle(3) = " C "
    Middle(4) = " D "
    Middle(5) = " E "
    RowCount = 1
    For LeftBit = 1 To 62
        For MidCount = 1 To 5
            For RightBit = 1 To 12
                Cells(RowCount, 1).Value = Format(LeftBit, "000") + Middle(MidCount) + Format(RightBit, "000")
                RowCount = RowCount + 1
            Next RightBit
        Next MidCount
    Next LeftBit
    Application.ScreenUpdating = True
End Sub

 

[bluesquared]

Hi there welcome to the forum. This piece of code will generate all your combinations in column A, starting at A1:

Perfect, thanks very much, John!

 

[jmacleary]

You're welcome and thanks for the feedback.