0

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 16807 combinations

A   B   C   D   E
54  23  43  1   1
21  45  433 5   51
25  65  456 3   3
65  66  5678    100 5
12  77  980 5   6
13  90  878 6   66
111 909 662 50  55

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  1   5
54  23  43  1   6
54  23  43  1   66
54  23  43  1   55
54  23  43  5   1
54  23  43  5   51
54  23  43  5   3
54  23  43  5   5
54  23  43  5   6
54  23  43  5   66
54  23  43  5   55

…               
…               
111 909 662 50  55

Manually it will take forever and frankly speaking, I'm not an expert in Excel. Please help me.

  • Can you clarify how you get 16807? C(35,5) is 324632. – XOR LX Aug 20 '15 at 14:57
  • How this different from your previous question? – Kyle Aug 20 '15 at 15:25
0

Running this short macro:

Sub Combin()
    Dim i As Long, j As Long, k As Long, l As Long
    Dim nRow As Long
    nRow = 1
    With Sheets("Sheet1")
    For i = 1 To 7
      ii = .Cells(i, "A").Value
      For j = 1 To 7
        jj = .Cells(j, "B").Value
        For k = 1 To 7
          kk = .Cells(k, "C").Value
          For l = 1 To 7
            ll = .Cells(l, "D").Value
            Sheets("Sheet2").Cells(nRow, "A").Value = ii
            Sheets("Sheet2").Cells(nRow, "B").Value = jj
            Sheets("Sheet2").Cells(nRow, "C").Value = kk
            Sheets("Sheet2").Cells(nRow, "D").Value = ll
            nRow = nRow + 1
          Next l
        Next k
      Next j
    Next i
    End With
End Sub

will take data on Sheet1 like:

enter image description here

and produce data like:

enter image description here

on Sheet2 for all 2401 combinations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.