Create Permutations [UDF] - Get Digital Help

Author: Oscar Cronquist Article last updated on January 31, 2025

I discussed the difference between permutations and combinations in my last post, today I want to talk about two kinds of permutations, with repetition and without repetition.

What's on this webpage

1. Permutations with repetition
2. Constructing a permutations array with binary values
3. Permutations without repetition
4. Create permutations - UDF
5. List permutations no repetition - UDF

1. Permutations with repetition

I explained in my last post that phone numbers are permutations because the order is important. But phone numbers may also contain duplicate numbers or repeated numbers like 11 234, here number 1 is repeated. A digit in a phone number has 10 different values, 0 to 9. A five digit phone number has 10x10x10x10x10 or 10^5 equals 100 000 permutations.

Another example with repetitive numbers are bits and bytes. A bit is a single binary number like 0 or 1. A byte is a sequence of bits and eight bits equal one byte. A byte contains 256 different permutations and repetition is allowed.

Here is how you calculate the number of permutations. There are two different values 0 and 1 (binary) and a byte has 8 binary values. 2x2x2x2x2x2x2x2 or 2^8 equals 256 permutations.

Did you know? The UTF-8 is a character encoding scheme using 8 bits to encode all possible characters, it is the most used encoding system on world wide web today.

Back to top

2. Constructing a permutations array with binary values

Excelxor showed us how to build a permutations array using only excel functions, in one of his blog posts.

The picture to the left is an array made by excelxor's formula, this example uses four cells or 4 bits. 2x2x2x2 or 2^4 equals 16 permutations.

With this type of array excelxor finds which numbers add up to a total.

Array formula:

=MOD(INT(( ROW( INDIRECT("1:"&2^ROWS($A$2:$A$5)))-1 )/2^( TRANSPOSE( ROW( INDIRECT("1:"&ROWS($A$2:$A$5))))-1 )), 2)

I have made a blog post a few years ago how to calculate permutations with repetition with a custom function.

Back to top

Explaining array formula

Step 1 - Calculate number of rows

The ROWS function returns the number of rows in a cell range.

ROWS($A$2:$A$5)

returns 4.

Step 2 - Concatenate characters

The ampersand character concatenates strings and numbers.

"1:"&ROWS($A$2:$A$5) returns  "1:4"

Step 3 - Create a cell reference

The INDIRECT function returns the reference specified by a text string.

INDIRECT("1:"&ROWS($A$2:$A$5)) returns cell reference 1:4

Step 4 - Return row numbers from cell reference

The ROW function returns the row number from a cell reference.

ROW( INDIRECT("1:"&ROWS($A$2:$A$5))) returns {1; 2; 3; 4}. These numbers are in a vertical range.

Step 5 - Transpose numbers

The TRANSPOSE function converts a vertical range to a horizontal range or vice versa.

TRANSPOSE( ROW( INDIRECT("1:"&ROWS($A$2:$A$5)))) returns {1, 2, 3, 4}.

Step 6 - Subtract with 1

( TRANSPOSE( ROW( INDIRECT("1:"&ROWS($A$2:$A$5))))-1 ) returns {0, 1, 2, 3}

Step 7 - Remove decimals from numbers

The INT function removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to the nearest integer.

INT(( ROW( INDIRECT("1:"&2^ROWS($A$2:$A$5)))-1 )/2^( TRANSPOSE( ROW( INDIRECT("1:"&ROWS($A$2:$A$5))))-1 ))

returns {0, 0, 0, ... , 1}.

Step 8 - Create binary array

The MOD function returns the remainder after a number is divided by divisor.

MOD(INT(( ROW( INDIRECT("1:"&2^ROWS($A$2:$A$5)))-1 )/2^( TRANSPOSE( ROW( INDIRECT("1:"&ROWS($A$2:$A$5))))-1 )), 2)

returns {0, 0, 0, ... , 1}.

Back to top

3. Permutations without repetition

Imagine constructing an anagram of a word "police". You are allowed to rearrange the letters but you can´t repeat a letter. See picture to the right.

Another example is how many times can you rearrange 6 people around a table? Most people will get annoyed if you ask them to change seat more than once but in this example you can rearrange as many times as you like. How many permutations are there? You can´t have a person on two chairs at the same time, repetition is not allowed.

Here is a link to a udf I made a few years ago: Excel udf: List permutations without repetition

Tip! Use the PERMUT function to calculate permutations. The word police has 6 letters, how many permutations without repetition are there if you choose 6 out of 6 letters? =PERMUT(6,6) equals 720 permutations.

Back to top

4. Create permutations - UDF

Chris asks:

Maximum Number Allowed is 4 digit and the number is from 0 to 9.

After I fill in the number i want it to automatic 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.

Answer:

This udf creates permutations from a text string. You can also choose how many letters in each permutation.

Array formula in cell A3:A26:

=ListPermut("1234",4)

How to create this array formula

1. Select cell range A3:A26
2. Type above array formula in formula bar
3. Press and hold Ctrl + Shift
4. Press Enter once
5. Release all keys

VBA code

Function ListPermut(str As String, num As Integer) 'Permutations without repetition Dim c, r, p As Long Dim rng() As Long, temp As Long, i As Long Dim temp1 As Long, y() As Long, d As Long Dim tmpOut(), tmpArr() As Variant Dim j As Integer Dim a As Boolean ReDim tmpArr(0) ReDim tmpOut(0) For j = 1 To Len(str) tmpArr(UBound(tmpArr)) = Mid(str, j, 1) ReDim Preserve tmpArr(UBound(tmpArr) + 1) Next j ReDim Preserve tmpArr(UBound(tmpArr) - 1) p = WorksheetFunction.Permut(Len(str), Len(str)) ReDim rng(1 To p, 1 To Len(str)) For c = 1 To Len(str) rng(1, c) = c Next c For r = 2 To p For c = 1 To num tmpOut(UBound(tmpOut)) = tmpOut(UBound(tmpOut)) & tmpArr(rng(r - 1, c) - 1) Next c If UBound(tmpOut) <> 0 Then If tmpOut(UBound(tmpOut)) = tmpOut(UBound(tmpOut) - 1) Then tmpOut(UBound(tmpOut)) = "" Else ReDim Preserve tmpOut(UBound(tmpOut) + 1) End If Else ReDim Preserve tmpOut(UBound(tmpOut) + 1) End If For c = Len(str) To 1 Step -1 If rng(r - 1, c - 1) < rng(r - 1, c) Then temp = c - 1 Exit For End If Next c For c = Len(str) To 1 Step -1 rng(r, c) = rng(r - 1, c) Next c For c = Len(str) 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(Len(str) - temp) i = 0 For d = temp + 1 To Len(str) y(i) = rng(r, d) i = i + 1 Next d i = 0 For d = Len(str) To temp + 1 Step -1 rng(r, d) = y(i) i = i + 1 Next d Exit For End If Next c If r = p Then For c = 1 To num tmpOut(UBound(tmpOut)) = tmpOut(UBound(tmpOut)) & tmpArr(rng(r, c) - 1) Next c If tmpOut(UBound(tmpOut)) = tmpOut(UBound(tmpOut) - 1) Then ReDim Preserve tmpOut(UBound(tmpOut) - 1) End If End If Next r ListPermut = Application.Transpose(tmpOut) End Function

Where to copy vba code?

Press Alt+F11

Get the Excel file

Chris_ListPermut_norep.xls

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

1. Copy the user defined function below
2. Press Alt-F11 to open the Visual Basic editor
3. Press with left mouse button on Module on the Insert menu
4. Paste to code module
5. Exit visual basic editor and go back to Excel

Note: Save your workbook with file extension *.xlsm (Macro-enabled workbook) to keep the function attached to your workbook.

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

1. Select cell range B3:E26, see top image above.
2. Type =ListPermut(4).
3. Enter the user defined function as an array formula.
   1. Press and hold CTRL + SHIFT simultaneously.
   2. Now press Enter once.
   3. 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.

Get the Excel file

ListPermut_norep.xls

Permutations category

Find numbers closest to sumExcelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […] Rotating unique groups with no repeatThis article demonstrates ways to create unique groups with no repeat. The first example shows a formula that returns random […] More than 1300 Excel formulas

Excel categories

Home page Excel Functions Excel Formulas Excel Tables Advanced Filter Data Validation Drop Down List Named Ranges Excel Solver Charts Conditonal Formatting Pivot Tables VBA Macros UDFs Archive

Comments (28)