## List permutations with repetition [UDF]

*Article updated on February 13, 2018*

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

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 […]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 […]### 4 Responses to “List permutations with repetition [UDF]”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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