Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 57
Default 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.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,346
Default 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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create a complete list of combinations from 5 options Nevermore Excel Discussion (Misc queries) 6 July 5th 09 04:04 PM
List of Permutations when 2 letter is taken at a time Prem Excel Discussion (Misc queries) 5 May 21st 08 07:04 PM
List of Permutations Prem Excel Discussion (Misc queries) 1 May 21st 08 09:19 AM
Permutations or Combinations or some other function?? Mark Siler Excel Discussion (Misc queries) 4 December 23rd 06 04:22 PM
How do I create non recurring permutations andrewblack Excel Discussion (Misc queries) 0 February 16th 06 12:54 AM


All times are GMT +1. The time now is 09:27 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"