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

VBA code:

Option Explicit
Function ListPermut(num As Integer)
'Permutations without repetition
Dim c As Long, r As Long, p As Long
Dim rng() As Long, temp As Long, i As Long
Dim temp1 As Long, y() As Long, d As Long
p = WorksheetFunction.Permut(num, num)
' Create array
ReDim rng(1 To p, 1 To num)
'Create first row in array (1, 2, 3, ...)
For c = 1 To num
  rng(1, c) = c
Next c
For r = 2 To p
' 1. Find the first smaller number rng(r-1, c-1)<rng(r-1,c)
  For c = num To 1 Step -1
    If rng(r - 1, c - 1) < rng(r - 1, c) Then
      temp = c - 1
      Exit For
    End If
  Next c
' Copy values from previous row
For c = num To 1 Step -1
rng(r, c) = rng(r - 1, c)
Next c
' 2. Find a larger number than rng(r-1, temp)as far to the right as possible
For c = num To 1 Step -1
  If rng(r - 1, c) > rng(r - 1, temp) Then
    temp1 = rng(r - 1, temp)
    rng(r, temp) = rng(r - 1, c)
    rng(r, c) = temp1
    ReDim y(num - temp)
    i = 0
    For d = temp + 1 To num
      y(i) = rng(r, d)
      i = i + 1
    Next d
    i = 0
    For d = num To temp + 1 Step -1
      rng(r, d) = y(i)
      i = i + 1
    Next d
    Exit For
  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

Example,

  1. Select sheet1
  2. Select cell range A1:D24
  3. Type ListPermut(number) in formula bar.
  4. Press CTRL + SHIFT + ENTER

Download excel file

ListPermut_norep.xls
(Excel 97-2003 Workbook *.xls)