# how to generate all possibilities of combination for a set of number or range?

reference to question and answer below, how to generate all possible 3 digit combination of the numbers 0-6

i have similar case (need to generate all possible combinations) but for set of values like in table below, total possibilities will be 3024 combinations

``````    A   B   C   D   E
54  23  43  1   1
21  45  433 2   51
25  65  456 3   3
65  66  5678    100
77  980 5
90  878 6
909
``````

combinations will look like

``````A   B   C   D   E
54  23  43  1   1
54  23  43  1   51
54  23  43  1   3
54  23  43  2   1
54  23  43  2   51
54  23  43  2   3
...
...
...

65  909 878 6   3
``````

manually it will take forever and frankly speaking, I'm not an expert in excel. please help me.

this would be much easier using a programming language. Just load your values into arrays (a, b, c, d, e), and then iterate through them in nested for loops. Maybe something like this could apply to excel?

Here is a script that can be easily tweaked in vba to do what you want.

It assumes the data is in columns A-C (no header) and pastes in columns E-G. For more input columns you would need more nested loops. Also could be some tweaks depending on where you want to paste data but should get you 90% of the way there.

``````Sub calcCombinations()
Dim lastRowA As Long, lastRowB As Long, lastRowC As Long
Dim CurRowA As Long, CurRowB As Long, CurRowC As Long, pasteRow As Long

pasteRow = 1
With ActiveSheet
lastRowA = .Range("A" & .Rows.Count).End(xlUp).Row
lastRowB = .Range("B" & .Rows.Count).End(xlUp).Row
lastRowC = .Range("C" & .Rows.Count).End(xlUp).Row

For CurRowA = 1 To lastRowA
For CurRowB = 1 To lastRowB
For CurRowC = 1 To lastRowB
.Range("E" & pasteRow).Value = .Range("A" & CurRowA).Value
.Range("F" & pasteRow).Value = .Range("B" & CurRowB).Value
.Range("G" & pasteRow).Value = .Range("C" & CurRowC).Value
pasteRow = pasteRow + 1
Next
Next
Next
End With

End Sub
``````