## List permutations without repetition [UDF]

*Article last updated on January 30, 2018*

This blog post describes how to create permutations. Repetition is NOT allowed.

### VBA code:

Option Explicit 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 implement user defined function in excel

- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Copy and paste the above user defined function
- Exit visual basic editor

Example,

- Select sheet1
- Select cell range A1:D24
- Type ListPermut(
*number*) in formula bar. - Press CTRL + SHIFT + ENTER

### Download excel file

ListPermut_norep.xls

(Excel 97-2003 Workbook *.xls)

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

Select numbers in each permutation

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 table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

List files in a folder and subfolders [UDF]

This blog post describes how to list files in a folder and subfolders using vba. Where to copy vba code? […]

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

Use the img tag, like this: <img src="Insert pic link here">

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