get-digital-help.com

Noel asks:

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?

1. Press Alt-F11 to open visual basic editor
2. Click Module on the Insert menu
3. Copy and paste code above to the code module
   
4. Exit visual basic editor (Alt+Q)
5. Save your workbook as a *.xlsm file

How to run macro?

1. Press Alt+F8 to open the macro dialog box
2. Click ListPermut macro
3. 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

listpermutations.xlsm

Related articles

---

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 […]

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 […]

Create permutations [UDF]

Chris asks: Maximum Number Allowed is 4 digit and the number is from 0 to 9. After I fill in […]

Return all combinations

Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What […]

Permutations with and without repetition

I discussed the difference between permutations and combinations in my last post, today I want to talk about two kinds […]