All possible combination chart

[Patmustard]

Hi,
I'm trying to create an excel chart that will display all possible combinations of four variables. For example, I have the following possible fields:
Alpha: A,B
Numeric: 1,2
Symbol: @&
Alphanumeric: X1,Y1

Rather than having to manually create all the possible strings of the above, as below:
A,1,@,X1
A,1,@,Y1
B,1,@,X1
etc.

I'm trying to create a macro that will create it automatically.

Is this possible ?
Thanks !

 

[kpark91]

Hi, welcome to the board.

Yes, this is possible and the best way to approach this is to first
split each field in columnA - D.
this means your data table will look like
A, 1, @, X1
B, 2, , Y1

Then you will need a code to loop through each columns and create the combinations and fill them in columnE.

You can do it with the format you have but the method I've provided is definitely easier on the computer.

Anyways, after you've done that you can use this code

Sub Test()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim i&, j&, k&, l&
    For i = 1 To Range("A" & Rows.count).End(xlUp).Row
        For j = 1 To Range("B" & Rows.count).End(xlUp).Row
            For k = 1 To Range("C" & Rows.count).End(xlUp).Row
                For l = 1 To Range("D" & Rows.count).End(xlUp).Row
                    Range("E" & count).Value = Range("A" & i).Value & ", " & Range("B" & j).Value _
                                            & ", " & Range("C" & k).Value & ", " & Range("D" & l).Value
                Next l
            Next k
        Next j
    Next i
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

 

[moonfish]

I'm not sure which you would prefer but I was almost done writing a version with variables stored in vba when kpark posted his code:

Sub PermuteSet()
    Dim i, alpha, numeric, symbol, anum As Integer
    Dim alpArr(1), symArr(1), anumArr(1) As String
    alpArr(0) = "A"
    alpArr(1) = "B"
    symArr(0) = "@"
    symArr(1) = "&"
    anumArr(0) = "X1"
    anumArr(1) = "Y1"
    i = 1
    
    For alpha = LBound(alpArr) To UBound(alpArr)
        For numeric = 1 To 2
            For symbol = LBound(symArr) To UBound(symArr)
                For anum = LBound(anumArr) To UBound(anumArr)
                    Cells(i, 1) = alpArr(alpha) & "," & numeric & "," & symArr(symbol) & "," & anumArr(anum)
                    i = i + 1
                Next anum
            Next symbol
        Next numeric
    Next alpha
End Sub

 

[Patmustard]

Hi - many thanks for getting back so quickly. I can see what you're doing, but I get a 'run time error 1004': 'Method 'Range of object'_Global' failed
grrrr !

 

[kpark91]

Hi,
I'm guessing you've tried moonfish's code.

Try this then

Sub PermuteSet()
    Dim i%, alpha%, numeric%, symbol%, anum As Integer
    Dim alpArr(0 To 1) As String , symArr(0 To 1) As String, anumArr(0 to 1) As String
    alpArr(0) = "A"
    alpArr(1) = "B"
    symArr(0) = "@"
    symArr(1) = "&"
    anumArr(0) = "X1"
    anumArr(1) = "Y1"
    i = 1
    
    For alpha = LBound(alpArr) To UBound(alpArr)
        For numeric = 1 To 2
            For symbol = LBound(symArr) To UBound(symArr)
                For anum = LBound(anumArr) To UBound(anumArr)
                    Cells(i, 1) = alpArr(alpha) & "," & numeric & "," & symArr(symbol) & "," & anumArr(anum)
                    i = i + 1
                Next anum
            Next symbol
        Next numeric
    Next alpha
End Sub

 

[West Man]

A formula solution might be: in A1 through A16 =DEC2BIN(ROW()-1)+1111 and in B1 through B16 =CHOOSE(LEFT(A1,1),"A","B")&CHOOSE(MID(A1,2,1),1,2)&CHOOSE(MID(A1,3,1),"@","&")&CHOOSE(RIGHT(A1,1),"X1","Y1")