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

1. Press Alt-F11 to open visual basic editor
2. Click Module on the Insert menu
3. Copy and paste the above user defined function
4. Exit visual basic editor
5. Select sheet1
6. Select cell range A1:C27
7. 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