## 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 - http://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

### Permutations

Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What is a combination? To explain combinations I must explain the difference between combinations and permutations. Think of permutations as if the order is […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to total? (2): Multiple Solutions. The MMULT function is used in really clever way, I thought that function was pretty much useless. […]

I got a question a while ago about permutations, in essence how to find every permutation between 0 and 9 000 000 000, if the sum of all digits in a number is smaller or equal to 9. You can […]

### User defined functions udf

The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. However if your excel version is missing the TEXTJOIN function you can use a User Defined Function, I have all […]

You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website. I would choose the advanced filter […]

Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What is a combination? To explain combinations I must explain the difference between combinations and permutations. Think of permutations as if the order is […]

### Vba

You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website. I would choose the advanced filter […]

In a previous post: How to create a dynamic pivot table and refresh automatically in excel I demonstrated how to refresh a pivot table when a sheet is activated. This post describes how to refresh a pivot table when data is […]

Today I´ll show you how to search all excel workbooks (xls, xlsx, xlsm) in a folder for a text string. The macro creates a new sheet containing the search result. Each result contains a link to a cell where the […]

### 2 Responses to “List permutations with repetition and how many to choose from”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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.