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 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 rowwise.
How to add the User defined Function to your workbook
 Copy the user defined function below
 Press AltF11 to open the Visual Basic editor
 Click 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(r1, c1)<rng(r1,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(r1, 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.
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 [โฆ]
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 [โฆ]
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 [โฆ]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection [โฆ]
How to count word frequency in a cell range [UDF]
This user defined function creates a unique distinct list of words and how many times they occur in the selected [โฆ]
List files in a folder and subfolders [UDF]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is [โฆ]
5 Responses to โList permutations without 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.
Contact Oscar
You can contact me through this contact form
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 Nonrepeating
 Onerepeating 1123
 Dualdoubles 1122
 Onetripled 1112
 Quadruples 1111
Thank you in advance and very much appreciated.
 Nonrepeating 1234
 Onerepeating 1123
 Dualdoubles 1122
 Onetripled 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.