Combining all combinations of data from fourteen columns in Excel using VBA

Question

There were a couple conversations on here regarding VBA code to find all possible combinations among multiple columns with varying lengths of data in Excel. The conversations included 3, 4, and 5 columns, but I need to do this with 14 columns. The 5 column code given in this conversation is what I used: VBA - Write all possible combinations of 4 columns of data But I'm getting the following error: "Run-time error '6': Overflow" and it highlights this line when I go to debug:

Set out1 = Range("G2", Range("K2").Offset(UBound(c1) * UBound(c2) * UBound(c3) * UBound(c4) * UBound(c5) * UBound(c6) * UBound(c7) * UBound(c8) * UBound(c9) * UBound(c10) * UBound(c11) * UBound(c12) * UBound(c13) * UBound(c14)))

Here is the full code I tweaked from the example I found for 5 columns:

Sub combinations()

Dim c1() As Variant
Dim c2() As Variant
Dim c3() As Variant
Dim c4() As Variant
Dim c5() As Variant
Dim c6() As Variant
Dim c7() As Variant
Dim c8() As Variant
Dim c9() As Variant
Dim c10() As Variant
Dim c11() As Variant
Dim c12() As Variant
Dim c13() As Variant
Dim c14() As Variant
Dim out() As Variant
Dim j, k, l, m, n, o, p, q, r, s, t, u, v, w, x As Long


Dim col1 As Range
Dim col2 As Range
Dim col3 As Range
Dim col4 As Range
Dim col5 As Range
Dim col6 As Range
Dim col7 As Range
Dim col8 As Range
Dim col9 As Range
Dim col10 As Range
Dim col11 As Range
Dim col12 As Range
Dim col13 As Range
Dim col14 As Range
Dim out1 As Range


Set col1 = Range("A66", Range("A66").End(xlDown))
Set col2 = Range("B66", Range("B66").End(xlDown))
Set col3 = Range("C66", Range("C66").End(xlDown))
Set col4 = Range("D66", Range("D66").End(xlDown))
Set col5 = Range("E66", Range("E66").End(xlDown))
Set col6 = Range("F66", Range("F66").End(xlDown))
Set col7 = Range("G66", Range("G66").End(xlDown))
Set col8 = Range("H66", Range("H66").End(xlDown))
Set col9 = Range("I66", Range("I66").End(xlDown))
Set col10 = Range("J66", Range("J66").End(xlDown))
Set col11 = Range("K66", Range("K66").End(xlDown))
Set col12 = Range("L66", Range("L66").End(xlDown))
Set col13 = Range("M66", Range("M66").End(xlDown))
Set col14 = Range("N66", Range("N66").End(xlDown))

c1 = col1
c2 = col2
c3 = col3
c4 = col4
c5 = col5
c6 = col6
c7 = col7
c8 = col8
c9 = col9
c10 = col10
c11 = col11
c12 = col12
c13 = col13
c14 = col14

Set out1 = Range("P66", Range("AC66").Offset(UBound(c1) * UBound(c2) * UBound(c3) * UBound(c4) * UBound(c5) * UBound(c6) * UBound(c7) * UBound(c8) * UBound(c9) * UBound(c10) * UBound(c11) * UBound(c12) * UBound(c13) * UBound(c14)))
out = out1

j = 1
k = 1
l = 1
m = 1
n = 1
o = 1
p = 1
q = 1
r = 1
s = 1
t = 1
u = 1
v = 1
w = 1
x = 1

Do While j <= UBound(c1)
    Do While k <= UBound(c2)
        Do While l <= UBound(c3)
            Do While m <= UBound(c4)
                Do While n <= UBound(c5)
                    Do While o <= UBound(c6)
                        Do While p <= UBound(c7)
                            Do While q <= UBound(c8)
                                Do While r <= UBound(c9)
                                    Do While s <= UBound(c10)
                                        Do While t <= UBound(c11)
                                            Do While u <= UBound(c12)
                                                Do While v <= UBound(c13)
                                                    Do While w <= UBound(c14)
                                                        out(o, 1) = c1(j, 1)
                                                        out(o, 2) = c2(k, 1)
                                                        out(o, 3) = c3(l, 1)
                                                        out(o, 4) = c4(m, 1)
                                                        out(o, 5) = c5(n, 1)
                                                        out(o, 6) = c6(o, 1)
                                                        out(o, 7) = c7(p, 1)
                                                        out(o, 8) = c8(q, 1)
                                                        out(o, 9) = c9(r, 1)
                                                        out(o, 10) = c10(s, 1)
                                                        out(o, 11) = c11(t, 1)
                                                        out(o, 12) = c12(u, 1)
                                                        out(o, 13) = c13(v, 1)
                                                        out(o, 14) = c14(w, 1)
                                                        x = x + 1
                                                        w = w + 1
                                                    Loop
                                                    w = 1
                                                    v = v + 1
                                                Loop
                                                v = 1
                                                u = u + 1
                                            Loop
                                            u = 1
                                            t = t + 1
                                        Loop
                                        t = 1
                                        s = s + 1
                                    Loop
                                    s = 1
                                    r = r + 1
                                Loop
                                r = 1
                                q = q + 1
                            Loop
                            q = 1
                            p = p + 1
                        Loop
                        p = 1
                        o = o + 1
                    Loop
                    o = 1
                    n = n + 1
                Loop
                n = 1
                m = m + 1
            Loop
            m = 1
            l = l + 1
        Loop
        l = 1
        k = k + 1
    Loop
    k = 1
    j = j + 1
Loop


out1.Value = out

End Sub

Also, as a note, I tried tweaking the code so that my input started at the top of the spreadsheet (A1 vice the A66 I currently have), and that didn't help. Additionally, I know in the original code I used as reference it lists "Dim j As Long, k As Long, l As Long, etc.) and I shortened it, but I had originally used the long-form and still got the error. Any assistance would be greatly appreciated. I am a total amateur with VBA, so my apologies if the error is obvious. I have tried to research the error code, but I cannot find why mine specifically is messing up. Thanks very much for your time.

Very Respectfully, T.

Fourteen columns? How many values in each column? I suspect that would be a huge number of combinations... Edit: with only 3 values in each column you'd have about 4.7 million combinations — Tim Williams, Jul 13 '16 at 16:24
I imagine the result of offsetting by `UBound(c1) * UBound(c2) * UBound(c3) * UBound(c4) * UBound(c5) * UBound(c6) * UBound(c7) * UBound(c8) * UBound(c9) * UBound(c10) * UBound(c11) * UBound(c12) * UBound(c13) * UBound(c14)` number of rows is going to be beyond the number of rows on the sheet! — SierraOscar, Jul 13 '16 at 16:26
@TimWilliams, seven of the columns only have one value, while the largest has 8 values. Do you think the size is why I'm getting an error? Because I can try and combine attributes if 10 columns would be a more reasonable number. My end goal is to create a dashboard of sorts from survey data of items I have, so I need these combinations to populate it. Each of the columns has different independent attributes and scores, and I'd like to create a dashboard where I can use slicers to select various desirable attributes, and it will populate with the item that fits those attributes. — T. Classens, Jul 13 '16 at 16:42
Multiply all of the numbers of items together from all 14 columns - what is the result ? — Tim Williams, Jul 13 '16 at 16:44

arcadeprecinct · Accepted Answer · 2016-07-13T16:58:30.443

Overflow means the number is too high to be stored as the data type that you want it to be. The Offset argument is a Long so the maximum input value would be 2147483648 for it to not cause an overflow. Since you said your maximum column size is 8 and there are only 8 non-trivial columns, there has to be something else going on.

Ironically, the problem is caused by the columns with only one entry :)

You are setting the columns like this:

Set col1 = Range("A66", Range("A66").End(xlDown))

I am not going to go into this but if "A66" is the last cell with an entry in that column, .End(xlDown) will go all the way down to the bottom of the sheet. That's where your high numbers come from.

Use Cells(rows.count,1).End(xlUp) to find the last non-empty cell in column A:

Set col1 = Range("A66", Cells(rows.count,1).End(xlUp))

Of course this fixes only the Overflow problem (hopefully), you still might end up with something larger than your row count that takes a long time.

edit: btw, Dim i, j, k As Long only sets the last variable as Long, the others are set as Variant. It's the same as

Dim i
Dim j
Dim k as Long

Thanks very much for your response. I changed the portion you mentioned; here's the first line I changed: "Set col1 = Range("A66", Cells(Rows.Count, 1).End(xlUp))" However, then I got a Run-time error '13': Type mismatch for the line: "c1 = col1". I looked into it and saw another user had the same issue: http://stackoverflow.com/questions/28677005/excel-combination-generator I tried the answer given to add on the "If Not IsArray(c1) Then..." code, but I'm still getting the same 'Type mismatch' error message. Do you have any ideas on that? BTW, I also changed the As Long back to the original. — T. Classens, Jul 13 '16 at 20:19
@T.Classens Check if the range has only one cell before `c1=col1`. If so, create the array yourself like in the question you linked. If it doesn't work post it as a new question because it has nothing to do with the original question any more. — arcadeprecinct, Jul 14 '16 at 10:31

score 0 · Answer 2 · answered Jul 13 '16 at 17:08

You can do this pretty much variable with:

Option Explicit
Sub test()
  Dim inputRng As Range
  Set inputRng = ThisWorkbook.Sheets("Sheet1").Range("A2:E5") 'change this to fit your needs
  Dim inputVal() As Variant
  ReDim inputVal(1 To inputRng.Columns.Count)
  Dim holder() As Variant
  Dim i, j, k, xCol, xRow
  j = 1: k = 1
  'load in values
  For Each xCol In inputRng.Columns
    If Len(xCol.Cells(2, 1)) Then
      xRow = xCol.Cells(1, 1).End(xlDown).Row
    Else
      xRow = xCol.Cells(1, 1).Row
    End If
    If xRow > (xCol.Rows.Count + xCol.Row - 1) Then xRow = (xCol.Rows.Count + xCol.Row - 1)
    ReDim holder(0 To xRow - xCol.Cells(1, 1).Row + 1)
    holder(0) = UBound(holder)
    j = j * holder(0)
    For i = 1 To holder(0)
      holder(i) = xCol.Cells(i).Value
    Next
    inputVal(k) = holder
    k = k + 1
  Next
  Dim outputVal() As Variant
  ReDim outputVal(1 To j, 1 To inputRng.Columns.Count)
  k = 1
  For i = UBound(outputVal, 2) To 1 Step -1
    For j = 0 To UBound(outputVal) - 1
      outputVal(j + 1, i) = inputVal(i)((Int(j / k) Mod inputVal(i)(0)) + 1)
    Next
    k = k * inputVal(i)(0)
  Next
  Dim outputRng As Range
  Set outputRng = ThisWorkbook.Sheets("Sheet1").Range("G1")  'set here the first cell to start output
  outputRng.Resize(UBound(outputVal), UBound(outputVal, 2)).Value = outputVal
End Sub

Just set the range for the input-values and the upper left cell for your output.

But keep in mind: if you get overflow for j: there are so many combinations, it simply is to much to handle. (And also would never fit in 1 sheet)
In that case, split up the whole process to 2 sections and then tell everyone to add the second part to every item in the first part... ... probably noone will do that :P

If you have any questions, just ask :)

Combining all combinations of data from fourteen columns in Excel using VBA

2 Answers2