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 […]
Chris asks: Maximum Number Allowed is 4 digit and the number is from 0 to 9. After I fill in […]
I discussed the difference between permutations and combinations in my last post, today I want to talk about two kinds […]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
I got a question a while ago about permutations, in essence how to find every permutation between 0 and 9 […]
This article demonstrates a macro that lets you create a list of permutations, repetitions are allowed, based on a number […]
User defined function category
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
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 […]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
This post describes how to split words in a cell range into a cell each using a custom function. I […]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]
This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
Excel categories
7 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?