List All possible combinations

[flyingfawkes]

I am looking for a way to have excel list out all possible combinations where a few of the digits can be a variety of characters. See below example

W P L 1 T 7 Q 9 3 B
2 R 0 P
T P
6 T

So each combination would be...
WPL1T7Q93B
WPL1T7P93B
WPL107Q93B
WPL107P93B
WPL1P7Q93B
ETC....

The characters only appear in that order so for example the W,2,T,6 are always the first digit position. The biggest problem is that I do not know how many different characters can possibly be in each position and they will not always have the same amount of different characters.

 

[Eric W]

Welcome to the forum.

Your sample did not have the columns line up, so I guessed with this table:

	A	B	C	D	E	F	G	H	I	J
1	W	P	L	1	T	7	Q	9	3	B
2	2				0		P
3	T				P
4	6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

On Sheet1 of a new workbook, put your values in as shown. Each column can have from 1 to n entries, and the columns can go over to the right as far as needed. Make sure you have an empty Sheet2.

Then press Alt-F11 to open the VBA editor. From the menu select Insert > Module. Paste this code onto the sheet that opens:

Sub Permute()
Dim ix(100, 1) As Long, rc As Long, m As Long, br As Long, md As Variant, i As Long, r As Long
Dim str1 As String

    rc = Cells(1, Columns.Count).End(xlToLeft).Column
    m = 0
    For i = 1 To rc
        br = Cells(Rows.Count, i).End(xlUp).Row
        If br > m Then m = br
        ix(i, 0) = br
        ix(i, 1) = 1
    Next i
    md = Range(Cells(1, 1), Cells(m, rc)).Value

    r = 0
Incr:
    str1 = ""
    For i = 1 To rc
        str1 = str1 & md(ix(i, 1), i)
    Next i
    r = r + 1
    Sheets("Sheet2").Cells(r, "A") = str1
    
    For i = rc To 1 Step -1
        ix(i, 1) = ix(i, 1) + 1
        If ix(i, 1) <= ix(i, 0) Then Exit For
        ix(i, 1) = 1
    Next i
    If i > 0 Then GoTo Incr:
    
End Sub

Now close the editor and switch back to the Excel Sheet1. Press Alt-F8, select Permute and click Run. This will create a list of all possible permutations on Sheet2. Be aware that permutations grow rapidly, and depending on your input table, you could have a huge list to sort through.

Hope this helps.

 

[flyingfawkes]

This seems to work perfectly! Thank you so much!

Welcome to the forum.

Your sample did not have the columns line up, so I guessed with this table:

	A	B	C	D	E	F	G	H	I	J
1	W	P	L	1	T	7	Q	9	3	B
2	2						P
3	T				P
4	6

<tbody>
</tbody>

Sheet1​

On Sheet1 of a new workbook, put your values in as shown. Each column can have from 1 to n entries, and the columns can go over to the right as far as needed. Make sure you have an empty Sheet2.

Then press Alt-F11 to open the VBA editor. From the menu select Insert > Module. Paste this code onto the sheet that opens:

Sub Permute()
Dim ix(100, 1) As Long, rc As Long, m As Long, br As Long, md As Variant, i As Long, r As Long
Dim str1 As String

    rc = Cells(1, Columns.Count).End(xlToLeft).Column
    m = 0
    For i = 1 To rc
        br = Cells(Rows.Count, i).End(xlUp).Row
        If br > m Then m = br
        ix(i, 0) = br
        ix(i, 1) = 1
    Next i
    md = Range(Cells(1, 1), Cells(m, rc)).Value

    r = 0
Incr:
    str1 = ""
    For i = 1 To rc
        str1 = str1 & md(ix(i, 1), i)
    Next i
    r = r + 1
    Sheets("Sheet2").Cells(r, "A") = str1
    
    For i = rc To 1 Step -1
        ix(i, 1) = ix(i, 1) + 1
        If ix(i, 1) <= ix(i, 0) Then Exit For
        ix(i, 1) = 1
    Next i
    If i > 0 Then GoTo Incr:
    
End Sub

Now close the editor and switch back to the Excel Sheet1. Press Alt-F8, select Permute and click Run. This will create a list of all possible permutations on Sheet2. Be aware that permutations grow rapidly, and depending on your input table, you could have a huge list to sort through.

Hope this helps.

 

[Eric W]

Happy to help!

Thanks for the feedback.

 

[S.H.A.D.O.]

Very nice Eric W,

I like the code you posted and can use this in a few scenarios of my own.
Just one small question please, is it possible to separate the numbers within each combinations produced with a comma?

Thanks in advance.

 

[Eric W]

Sure!

Just change this line:

str1 = str1 & md(ix(i, 1), i)

to:

str1 = str1 & md(ix(i, 1), i) & IIf(i < rc, ", ", "")

 

[S.H.A.D.O.]

Brilliant, thanks Eric W, I appreciate it.