## 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.

**Contact Oscar**

You can contact me through this contact form

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.