How to create a list of permutations and combinations?

[Malcolm]

I have a matrix, 6 columns x 2 rows, which contains letter codes. Example:

A C E R Q L
B D K S P N

Each column is a choice; that is, either A or B; either C or D, etc.

I need to create a list of all possible permutations.
There are 64 permutations, e.g.
ACERQL
ACERQN
ACERPL
ACERPN
etc.

The letters in my 12 cells sometimes change based on formulas elsewhere in
the document, so the list needs to change with them.

To make things even more complicated, some columns will sometimes have THREE
choices in them instead of two (again, changing based on formulas), so the
ideal answer would enable me to deal with those cases automatically.

 

[Shane Devenshire]

Actually it seems to me that the first letter could be anything from A to Z
and so on. So how do you determine what the possible choices are at any
given position. Also, I don't follow how the second line fits into the
problem B D K.....? Also, you say there are 64 permutations but then you say
some items can have 3 choices, in which case how do you come to 64?

 

[Joel]

Try this code

Public InStrings
Public Combo() As Variant
Public RowCount
Public ComboLen
Sub combinations()


InStrings = Array("ACERQL", "BDKSPN")

ComboLen = Len(InStrings(0))
ReDim Combo(0 To (ComboLen - 1))

Level = 1
RowCount = 1
Call Recursive(Level)

End Sub
Sub Recursive(ByVal Level As Integer)


For i = 0 To UBound(InStrings)

Combo(Level - 1) = Mid(InStrings(i), Level, 1)
If Level = ComboLen Then
For ColCount = 1 To ComboLen
Sheets("Sheet1").Cells(RowCount, ColCount) = Combo(ColCount - 1)
Next ColCount
RowCount = RowCount + 1
Else
Call Recursive(Level + 1)
End If

Next i
End Sub