List permutations with repetition and how many to choose from
Noel asks:
Is there a way where i can predict all possible outcomes in excel in the below example.
Total games are 13 (ABCDEFGHIJKLM). Possible outcomes are win(home team),draw or win(Away team) represented by 1,X or 2 respectively.Outcomes should be in the below formats
111111111111X
11111111111XX
2222222X22221
222222222222X
The following macro lets you enter text strings separated by a comma and the number of games.
Sub ListPermut() 'This macro creates a list of all permutations 'Get digital Help - https://www.get-digital-help.com/ 'Define variables Dim ws As Worksheet, Ans As String, ans1() As String, digits As Integer Dim num As Integer, p As Long, i As Long, t As Long Dim rng() As Long, c As Long, rng1() As String 'Insert a new sheet Set ws = Sheets.Add 'Ask for user input Ans = InputBox("Type strings separated with a comma:") digits = InputBox("How many strings?") 'Split text strings to an array ans1 = Split(Ans, ",") 'Count values in aray num = UBound(ans1) + 1 'Calculate number of permutations p = num ^ digits 'Redimension arrays ReDim rng(1 To digits) ReDim rng1(1 To digits) 'Save 1 to all values in first row of array For c = 1 To digits rng(c) = 1 Next c i = 0 'Don't show the result until finished Application.ScreenUpdating = False 'Repeat until all permutations have been created Do Until (i + t) = (p - 1) 'Use text strings instead of numbers For c = LBound(rng1) To UBound(rng1) rng1(c) = ans1(rng(c) - 1) Next c 'Transfer values from array to worksheet ws.Range("A1").Resize(, digits).Offset(i) = rng1 'Build next row of permutations For c = digits To 1 Step -1 If c = digits Then rng(c) = rng(c) + 1 ElseIf rng(c) = 0 Then rng(c) = rng(c - 1) End If If rng(c) = num + 1 Then rng(c) = 1 rng(c - 1) = rng(c - 1) + 1 End If Next c 'Count made permutations i = i + 1 'Insert a new sheet if rows exceed 999 999 If i = 1000000 Then Set ws = Sheets.Add t = t + 1000000 i = 0 End If Loop 'Use text strings instead of numbers For c = LBound(rng1) To UBound(rng1) rng1(c) = ans1(rng(c) - 1) Next c 'Transfer values from array to worksheet ws.Range("A1").Resize(, digits).Offset(i) = rng1 'Show output Application.ScreenUpdating = True End Sub
Where to copy code?
- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Copy and paste code above to the code module
- Exit visual basic editor (Alt+Q)
- Save your workbook as a *.xlsm file
How to run macro?
- Press Alt+F8 to open the macro dialog box
- Click ListPermut macro
- Click Run
Instructions
Enter your text strings using a comma as a text delimiting character.
Click OK button.
Enter number of games.
Click OK button.
Excel processes the data for a while and then creates two sheets with a total of 1594323 permutations.
The picture shows 14 out of 1594323 permutations.
Download excel *.xlsm file
Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What […]
Chris asks: Maximum Number Allowed is 4 digit and the number is from 0 to 9. After I fill in […]
List permutations without repetition [UDF]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
List permutations with repetition [UDF]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
How to use the PERMUTATIONA function
The PERMUTATIONA function returns the number of permutations for a specific number of elements that can be selected from a […]
Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What […]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
Rotating unique groups with no repeat
Kristina asks: Hi Oscar,Your formula works great, however, I was wondering if there is capability to add another countif criteria […]
2 Responses to “List permutations with repetition and how many to choose from”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I have 17 games to bet, and am suppose to choose win, loose and draw.
How can I create a table with 17,matches with win,lose and draw? How many table will appear?
jimmy munisi,
The outcome is either win, lose or draw, three possibilities.
There are 17 games. 3^17 = 129 140 163 permutations.
If you want to list all permutations in a workbook, each sheet has 1 048 576 rows.
129 140 163 / 1 048 576 = 123.1 sheets
You need 124 worksheets in a workbook to list all permutations.