List permutations no repetition [UDF]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning the order is important.
Examples of permutations are phone numbers, if you enter the digits in the wrong order you might phone someone else, however, phone numbers may have digits repeated and in that case repetition is allowed.
The image above demonstrates how to create permutations of a given number of items and repetition is not allowed, using a user defined function. The numbers are not repeated row-wise.
How to add the User defined Function to your workbook
- Copy the user defined function below
- Press Alt-F11 to open the Visual Basic editor
- Press with left mouse button on Module on the Insert menu
- Paste to code module
- Exit visual basic editor and go back to Excel
VBA code
Function ListPermut(num As Integer)
'Permutations without repetition
Dim c As Long, r As Long, p As Long
Dim rng() As Long, temp As Long, i As Long
Dim temp1 As Long, y() As Long, d As Long
p = WorksheetFunction.Permut(num, num)
' Create array
ReDim rng(1 To p, 1 To num)
'Create first row in array (1, 2, 3, ...)
For c = 1 To num
rng(1, c) = c
Next c
For r = 2 To p
' 1. Find the first smaller number rng(r-1, c-1)<rng(r-1,c)
For c = num To 1 Step -1
If rng(r - 1, c - 1) < rng(r - 1, c) Then
temp = c - 1
Exit For
End If
Next c
' Copy values from previous row
For c = num To 1 Step -1
rng(r, c) = rng(r - 1, c)
Next c
' 2. Find a larger number than rng(r-1, temp)as far to the right as possible
For c = num To 1 Step -1
If rng(r - 1, c) > rng(r - 1, temp) Then
temp1 = rng(r - 1, temp)
rng(r, temp) = rng(r - 1, c)
rng(r, c) = temp1
ReDim y(num - temp)
i = 0
For d = temp + 1 To num
y(i) = rng(r, d)
i = i + 1
Next d
i = 0
For d = num To temp + 1 Step -1
rng(r, d) = y(i)
i = i + 1
Next d
Exit For
End If
Next c
Next r
ListPermut = rng
End Function
How to enter the user defined function
- Select cell range B3:E26, see top image above.
- Type =ListPermut(4).
- Enter the user defined function as an array formula.
- Press and hold CTRL + SHIFT simultaneously.
- Now press Enter once.
- Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Permutations category
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 […]
This article demonstrates a formula What's on this page Question Formula Array formula Explaining array formula Get the Excel File […]
User defined function category
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
Excel categories
14 Responses to “List permutations no repetition [UDF]”
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.
Thank you for your guide.
After follow the instructions shown and still couldn't get what i want. I am very much appreciate if you could guide me to solve my matter. My problem is:
Explanation 1 (Permutations):
Maximum Number Allowed is 4 digit and the number is from 0 to 9.
After fill in the number i want it to automotic permutate the numbers and list in details, example if i key in 1234 and the list will be:
1234, 1243, 1423, 4123, 1324, 1342, 1432, 4132, 3124, 3142, 3412, 4312, 2134, 2143, 2413, 4213, 2314, 2341, 2431, 4231, 3214, 3241, 3421, 4321.
Explanation 2 (Combinations):
Maximum Number Allowed is 10 digit and the number is from 0 to 9.
After fill in the number i want it to automotic combine the numbers and list in details, example if i key in 01234567 and the list will be:
0012, 0112, 0122, 0013, 0113, 0133, 0014, 0114, 0144, 0015, 0115, 0155, 0016, 0116, 0166, 0017, 0117, 0177, 0023, 0223, 0233, 0024, 0224, 0244, 0025, 0225, 0255, 0026, 0226, 0266, 0027, 0227, 0277, 0034, 0334, 0344, 0035, 0335, 0355, 0036, 0336, 0366, 0037, 0337, 0377, 0045, 0445, 0455, 0046, 0446, 0466, 0047, 0447, 0477, 0056, 0556, 0566, 0057, 0557, 0577, 0067, 0667, 0677, 1123, 1223, 1233, 1124, 1224, 1244, 1125, 1225, 1255, 1126, 1226, 1266, 1127, 1227, 1277, 1134, 1334, 1344, 1135, 1335, 1355, 1136, 1336, 1366, 1137, 1337, 1377, 1145, 1445, 1455, 1146, 1446, 1466, 1147, 1447, 1477, 1156, 1556, 1566, 1157, 1557, 1577, 1167, 1667, 1677, 2234, 2334, 2344, 2235, 2335, 2355, 2236, 2336, 2366, 2237, 2337, 2377, 2245, 2445, 2455, 2246, 2446, 2466, 2247, 2447, 2477, 2256, 2556, 2566, 2257, 2557, 2577, 2267, 2667, 2677, 3345, 3445, 3455, 3346, 3446, 3466, 3347, 3447, 3477, 3356, 3556, 3566, 3357, 3557, 3577, 3367, 3667, 3677, 4456, 4556, 4566, 4457, 4557, 4577, 4467, 4667, 4677, 5567, 5667, 5677.
And will have the options of Non-repeating
- One-repeating 1123
- Dual-doubles 1122
- One-tripled 1112
- Quadruples 1111
Thank you in advance and very much appreciated.
- Non-repeating 1234
- One-repeating 1123
- Dual-doubles 1122
- One-tripled 1112
- Quadruples 1111
Thank you...
Can you help me make a permutation list for 6 slots, with a +1 or -1 value. There should be a total of 64 combinations.
hi,
i'm trying to use this function. I can generate only permutations only up to 8 as function argument, does it has a limit?
thanks in advance
Andrea
andrea,
Yes, there is a limit to the vba array.
sir i have f1,f2,f3,w1,w2,w3,g1,g2,g3,r1,r2,r3
i want to make max possible combination using one of f,one of w,one of g,one of r
how will i combine and pl display it
thanks eg f1,w1,g1,r1
f2,w1,g1,r1
can i get excel for 16 digit, possible permutations with out repetition of number?
hi,
can you modfied this macro for producing numbers
when order isn't important ?
like 5 out 6 of 1-2-3-4-5-6
total combinations = 6
{1,2,3,4,5} {1,2,3,4,6} {1,2,3,5,6} {1,2,4,5,6} {1,3,4,5,6} {2,3,4,5,6}
me,
there is an Excel 365 formula here: List combinations
oscar,
i'm with excel 21
don't have 365
but thanks
me,
read this: Return all combinations
hi oscar, didn't manage with it, get a an answer in another website
thanks anyway
Hello Oscar,
Thanks for the nice and informative website. I have extensively used formulas and methods described in this website for personal use.
I am trying to execute display of permutations from the given list:
Reagent 1 2 3 4
Lots
1 R1-1 R2-1 R3-1 R4-1
2 R2-2 R3-2 R4-2
3 R2-3
4 R2-4
I want to display combinations like this below:
R1-1 R1-1 R1-2 R1-2
R3-1 R3-2 R3-1 R3-2
R4-1 R4-2 R4-1 R4-2 R4-1 R4-2 R4-1 R4-2
R2-1 1 5 9 13 17 21 25 29
R2-2 2 6 10 14 18 22 26 30
R2-3 3 7 11 15 19 23 27 31
R2-4 4 8 12 16 20 24 28 32
Above example shows 32 permutations. I am working with 13 different reagents and may have lots upto 12. However, only some reagents will be subject to permutations such that total permutation does not exceed 96 nos.
The possible combinations in terms of Reagents x lots.
considering only two reagents under study can be 12 lots of Reagents-1 x 8 lots of Reagents-2 will have 96 combinations; similarly
considering all 13 reagents under study can be 7 lots of each Reagent can give max 1 combination.
I tried my level best to make you understand my problem.
Your help is highly appreciated.
Best Regards,
Shailesh
Shailesh
Can you provide an image of your data?
Perhaps this is helpful?
Create permutations using a formula