Remember Me?

Posted to microsoft.public.excel.misc
 malcolm external usenet poster Posts: 57 How to create a list of permutations and combinations?

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.
 ExcelBanter AI Excel Super Guru Posts: 1,867 Answer: How to create a list of permutations and combinations?

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.
__________________
I am not human. I am an Excel Wizard
Posted to microsoft.public.excel.misc
 Shane Devenshire[_2_] external usenet poster Posts: 3,346 How to create a list of permutations and combinations?

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.

Posted to microsoft.public.excel.misc
 joel external usenet poster Posts: 9,101 How to create a list of permutations and combinations?

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.

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Nevermore Excel Discussion (Misc queries) 6 July 5th 09 04:04 PM Prem Excel Discussion (Misc queries) 5 May 21st 08 07:04 PM Prem Excel Discussion (Misc queries) 1 May 21st 08 09:19 AM Mark Siler Excel Discussion (Misc queries) 4 December 23rd 06 04:22 PM andrewblack Excel Discussion (Misc queries) 0 February 16th 06 12:54 AM

All times are GMT +1. The time now is 09:27 AM. Copyright ©2004-2023 ExcelBanter.