## 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.

**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 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