## Excel udf: List permutations with repetition

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

### Vba code:

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 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
- Select sheet1
- Select cell range A1:C27
- Type
**=ListPermut(3)**in formula bar and press CTRL+SHIFT+ENTER

Example,

### Array formula in A3:C29:

### Download excel sample file for this tutorial

ListPermut.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

### Category: Permutations

Comments(37) Filed in category: Combinations, Excel, Permutations

Comments(13) Filed in category: Excel, MMULT function, Permutations, SUM function

Excel udf: List permutations without repetition

This blog post describes how to create permutations. Repetition is NOT allowed. VBA code: How to implement user defined function in […]Comments(5) Filed in category: Excel, Permutations

Permutations with and without repetition

I discussed the difference between permutations and combinations in my last post, today I want to talk about two kinds […]Comments(4) Filed in category: Excel, Permutations

List all permutations with a condition

I got a question a while ago about permutations, in essence how to find every permutation between 0 and 9 […]Comments(2) Filed in category: Permutations

List permutations with repetition and how many to choose from

Noel asks: Is there a way where i can predict all possible outcomes in excel in the below example. Total […]Comments(2) Filed in category: Excel, Permutations

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 […]Comments(2) Filed in category: Excel, Permutations

Rotating unique groups with no repeat

Kristina asks: Hi Oscar,Your formula works great, however, I was wondering if there is capability to add another countif criteria […]Comments(2) Filed in category: Excel, Permutations

### 4 Responses to “Excel udf: List permutations with repetition”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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