## List permutations with repetition [UDF]

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

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

List permutations without repetition [UDF]

This blog post describes how to create permutations. Repetition is NOT allowed. VBA code: How to implement user defined function in […]

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 […]

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 […]

How to use the PERMUTATIONA function

The PERMUTATIONA function returns the number of permutations for a specific number of elements that can be selected from a […]

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 […]

List permutations without repetition [UDF]

This blog post describes how to create permutations. Repetition is NOT allowed. VBA code: How to implement user defined function in […]

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? […]

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

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

**Contact Oscar**

You can contact me through this contact form

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