Number Permutations across multiple columns

[broncos347]

I have four columns which I are labelled as Performance (Column A), Safety (Column B), Probability (Column C) and Effect (Column D) and in each one of those columns there could be a number from between 1 and 5.

Is there a formula or a bit of VBA which would be able to generate every permutation of each line of numbers for each of the columns, as shown in the example below?

permutations.xlsm
	A	B	C	D
1	Performance	Safety	Probability	Effect
2	1	1	1	1
3	1	2	1	1
4	1	2	2	1
5	1	2	3	1
6	1	2	4	1
7	1	2	5	1
8	1	2	2	2
9	1	2	2	3
10	1	2	2	4
11	1	2	2	5
12	1	2	3	2
13	1	2	3	3
14	1	2	3	4
15	1	2	3	5
16	1	2	3	3
17	1	2	3	4
18	1	2	3	5
19	1	2	4	2
20	1	2	4	3
21	1	2	4	4
22	1	2	4	5
23	1	2	5	2
24	1	2	5	3
25	1	2	5	4
26	1	2	5	5
Sheet1

 

[Legacy 143009]

Not the fastest but this will work:

Sub test()
  Dim numbers As Variant, numLen As Integer
  Dim permutation As Long, r As Long, c As Long, i As Long
  numbers = Split("1,2,3,4,5", ",")
  numLen = 4
  permutation = (UBound(numbers) + 1) ^ numLen
  For c = 1 To numLen
    For r = 1 To permutation
      Cells(r, c).Value = numbers(i)
      If r Mod ((UBound(numbers) + 1) ^ (numLen - c)) = 0 Then
        i = IIf(i = 4, 0, i + 1)
      End If
    Next
  Next
End Sub

 

[broncos347]

worked a treat thanks very much

 

[Legacy 143009]

Glad it did work! Thanks for the feedback

EDIT: Even you can modify this line like this

 i = IIf(i = 4, 0, i + 1)

i = i Mod (UBound(numbers) + 1)

 

[FormR]

Hi, here's a formula that you could also try, where the n variable is the numbers to choose from and the c variable is the numbers to choose.

=LET(n,5,c,4,MOD(ROUNDUP(SEQUENCE(n^c)/(n^SEQUENCE(,c,c-1,-1)),0)-1,n)+1)

 

[Legacy 143009]

Last version will look like this:

Sub test()
  Dim numbers As Variant, permutLen As Integer, numLen As Integer
  Dim permutation As Long, r As Long, c As Long
  numbers = Split("1,2,3,4,5", ",")
  numLen = UBound(numbers) + 1
  permutLen = 4
  permutation = numLen ^ permutLen
  For c = 1 To permutLen
    For r = 1 To permutation
      Cells(r, c).Value = numbers(Int((r - 1) / (permutation / (numLen ^ c))) Mod numLen)
    Next
  Next
End Sub