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.

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?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Malcolm" wrote:

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.

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




"Malcolm" wrote:

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.

To create a list of all possible permutations and combinations from your matrix, you can use the "Combin" and "Permut" functions in Excel. Here are the steps to do it:

1. First, create a table with all the possible choices for each column. For example, for the first column, create a table with two rows, one for "A" and one for "B". For the second column, create a table with two rows, one for "C" and one for "D", and so on.
   
2. In a new column, use the "Combin" function to calculate the number of combinations for each column. For example, if a column has two choices, the formula would be "=COMBIN(2,1)" to calculate the number of combinations with one choice. If a column has three choices, the formula would be "=COMBIN(3,1)" for one choice, "=COMBIN(3,2)" for two choices, and so on.
   
3. In another new column, use the "Permut" function to calculate the number of permutations for each column. For example, if a column has two choices, the formula would be "=PERMUT(2,1)" to calculate the number of permutations with one choice. If a column has three choices, the formula would be "=PERMUT(3,1)" for one choice, "=PERMUT(3,2)" for two choices, and so on.
   
4. Calculate the total number of permutations by multiplying the number of permutations for each column. For example, if the first column has two choices and the second column has three choices, the total number of permutations would be "=PERMUT(2,1)*PERMUT(3,1)".
   
5. Create a new table with the same number of rows as the total number of permutations and the same number of columns as the original matrix. In each cell, use the "INDEX" function to select the appropriate choice from the table you created in step 1. For example, if the first column has two choices, the formula would be "=INDEX(Table1,1,RAND()*2+1)" to randomly select one of the two choices.
   
6. Repeat step 5 for each column, adjusting the formula as needed based on the number of choices for each column.
   
7. Copy the formulas down to fill the entire table with all possible permutations.

By following these steps, you should be able to create a list of all possible permutations and combinations from your matrix, even if the choices change based on formulas elsewhere in the document.