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

Question

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.

Answer 1

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?

Answer 2

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