List permutations with repetition [UDF]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the number of items to use and it will return an array of numbers.
Array formula:
To enter an array formula, type the formula in a cell then 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.
Vba
Function ListPermut(num As Integer) 'Permutations with repetition Dim c As Long, r As Long, p As Long Dim rng() As Long p = num ^ num ReDim rng(1 To p, 1 To num) For c = 1 To num rng(1, c) = 1 Next c For r = 2 To p For c = num To 1 Step -1 If c = num Then rng(r, c) = rng(r - 1, c) + 1 ElseIf rng(r, c) = 0 Then rng(r, c) = rng(r - 1, c) End If If rng(r, c) = num + 1 Then rng(r, c) = 1 rng(r, c - 1) = rng(r - 1, c - 1) + 1 End If Next c Next r ListPermut = rng End Function
How to add the user defined function to your workbook
The image above shows a macro that is not used in this article, it is only there to show you where to paste the code.
- Press Alt-F11 to open visual basic editor.
- Press with left mouse button on Module on the Insert menu.
- Copy the above user defined function.
- Paste it to the code module.
- Exit visual basic editor.
- Select sheet1.
- Select cell range A1:C27.
- Type =ListPermut(3) in formula bar and press CTRL+SHIFT+ENTER (Array formula).
If you don't know how to enter an array formula then read the detailed instructions below the image.
Example,
Array formula in A3:C29:
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 blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
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 […]
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 blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
This article demonstrates how to convert a range of cells containing strings separated by a delimiter into a range of […]
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, this article demonstrates two ways to handle more than one […]
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 […]
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
Macros and custom functions are great, they can automate many tedious tasks. To have them available whenever you need them, […]
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
Functions in this article
More than 1300 Excel formulas
Excel categories
4 Responses to “List permutations with 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.
I would need to use the ListPermut function with argument 7. As I can understand it only works to a maximum of 6.
Thanks,
Antonio
I'll post an answer as soon as I can.
I'm trying to use the function for obtaining the possibilities of distributing 3 things over 5 places (repetition allowed), but the function is not workings on this option, can you please help ??
example:
A,A,B,B,C
A,A,B,B,B
A,C,B,B,A
and so on....
Thanks in advance
and also the number 5 can be changed to be from 1 to 8 but the number 3 is constant. Thank you