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.

1. Press Alt-F11 to open visual basic editor.
2. Press with left mouse button on Module on the Insert menu.
3. Copy the above user defined function.
4. Paste it to the code module.
5. Exit visual basic editor.
6. Select sheet1.
7. Select cell range A1:C27.
8. 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:

Get the Excel file

ListPermut.xls

---

Weekly Blog EMAIL

[newsletter_signup_form id=1]

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---

Tracking a stock portfolio #2

This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to […]

How to use DIALOG BOXES

A dialog box is an excellent alternative to a userform, they are built-in to VBA and can save you time […]

Return all combinations

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

Dynamic Gantt charts

Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]

How to use Pivot Tables – Excel’s most powerful feature and also least known

A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.

Filter values in common between two cell ranges [UDF]

I tried the array formula in this post: Filter common values between two ranges using array formula in excel to […]