Using excel find all possible combinations

Question

Is there a way in excel to find out all possible combination of a table but each column can take 2 or 3 values. ?

Lets say this is what i need to sort :

A             A | B | C | D |
1             1 | 1 | 1 | 1 |
2             1 | 1 | 1 | 2 |
3             1 | 1 | 2 | 1 |
              1 | 2 | 1 | 1 |
              2 | 1 | 1 | 1 |
              ..............
              ...............
B 
1
2

C
1
2

D
1
2
3

More information is required - for example, where do the '3's come from in your left-hand column? — Lee Mac, Feb 25 '18 at 15:20
Hi lee, the ..... represent the continuation of the table... 2|2|2|2 and so on. The 3 will come 3|1|1|1 , 3|1|1|2 and so on and so on. Im trying to create/find a script that can fill all the matrix of that table. — Udi, Feb 25 '18 at 15:24

score 0 · Accepted Answer · answered Feb 25 '18 at 16:19

May be you’re after this (not tested but it should give you at least the idea)

Sub Combinations
    Dim i As Long, j As Long, k As Long, l As Long
    Dim iRow As Long
    For i = 1 To 3 ‘ change 3 to your first column actual last number
        For j = 1 To 2‘ change 2 to your second column avtual last number
            For k = 1 To 3 ‘ change 3 to your third column actual last number
                For l = 1 To 4 ‘ change 4 to your fourth column actual last number
                    iRow = iRow + 1
                    Cells(iRow,1).Resize(,4).Value = Array(i,j,k,l)
                Next
            Next
        Next
     Next
End Sub

Using excel find all possible combinations

1 Answers1