Generate all possible combinations given a variable number of columns

[450nick]

Hi all!

I have an input range where I can have up to 10 rows containing data, and this is repeated on up to 10 columns. so maximum input cells is 10x10 = 100. What I'm trying to do, is generate a list, of all possible combinations when picking one item from each of the available columns. I have this example macro that seems quite neat, but it doesn't quite work and I can't work out why. I'm looking for some help to either identify the error in this code, or show me a more efficient way to do this. I initially tried with a series of nested loops, but the changing number of columns got me stumped!

Thanks! Nick

Example code:

Sub ListCombinations()

Dim col As New Collection
Dim c As Range, sht As Worksheet, res
Dim i As Long, arr, numCols As Long

    Set sht = ActiveSheet
    For Each c In sht.Range("A1:J1").Cells
        col.Add Application.Transpose(sht.Range(c, c.End(xlDown)))
        numCols = numCols + 1
    Next c

    res = Combine(col, "~~")

    For i = 0 To UBound(res)
        arr = Split(res(i), "~~")
        sht.Range("H1").Offset(i, 0).Resize(1, numCols) = arr
    Next i

End Sub


'create combinations from a collection of string arrays
Function Combine(col As Collection, SEP As String) As String()

    Dim rv() As String
    Dim pos() As Long, lengths() As Long, lbs() As Long, ubs() As Long
    Dim t As Long, i As Long, n As Long, ub As Long
    Dim numIn As Long, s As String, r As Long

    numIn = col.Count
    ReDim pos(1 To numIn)
    ReDim lbs(1 To numIn)
    ReDim ubs(1 To numIn)
    ReDim lengths(1 To numIn)
    t = 0
    For i = 1 To numIn  'calculate # of combinations, and cache bounds/lengths
        lbs(i) = LBound(col(i))
        ubs(i) = UBound(col(i))
        lengths(i) = (ubs(i) - lbs(i)) + 1
        pos(i) = lbs(i)
        t = IIf(t = 0, lengths(i), t * lengths(i))
    Next i
    ReDim rv(0 To t - 1) 'resize destination array

    For n = 0 To (t - 1)
        s = ""
        For i = 1 To numIn
            s = s & IIf(Len(s) > 0, SEP, "") & col(i)(pos(i)) 'build the string
        Next i
        rv(n) = s

        For i = numIn To 1 Step -1
            If pos(i) <> ubs(i) Then   'Not done all of this array yet...
                pos(i) = pos(i) + 1    'Increment array index
                For r = i + 1 To numIn 'Reset all the indexes
                    pos(r) = lbs(r)    '   of the later arrays
                Next r
                Exit For
            End If
        Next i
    Next n

    Combine = rv
End Function

 

[mikerickson]

Inside this loop is where the delimiters are added and can be controlled.

For i = 1 To colCount
    oneArray = arrValues(i)
    BlankFlag = BlankFlag Or (oneArray(arrIndexes(i), 1) = vbNullString)
    Select Case i
        Case 1, 2
             oneResult = oneResult & "|" & oneArray(arrIndexes(i), 1)
        Case Else
             oneResult = oneResult & " " & oneArray(arrIndexes(i), 1)
      End Select
Next i

 

[450nick]

What about this loop? Should I pull all data in one long line of 62 columns into the array and then use the aforementioned code to assign what each column does?

    For i = 1 To colCount
        With Columns(i + (StartCol - 1))
            r = .Find("*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row
            arrValues(i) = Range(.Cells(1, 1), .Cells(r)).Value
        End With
        DelimiterCatch = DelimiterCatch & Delimiter
        arrIndexes(i) = 1
    Next i

 

[mikerickson]

The OP was about the possiblity of generating combinations from a 10 row 10 column range. What is this 62 column ? If you are making all the combinations of 62 columns, that's not really useful.

 

[450nick]

Sorry not 62, 60 columns. So it's still 10x10, but for each value there are 5 columns of data. The first column contains the value that is built into the string as we have already done. Attached to each value, there are 5 numbers in the columns beside. As the string is built, I also need to sum up the 5 values from each component of the string, so my output will be a string with delimiters in between, and 5 numbers beside. Might be easier to explain if I could show you the sheet.

 

[450nick]

Sorry quite hard to explain but here's an example.

So in this case, I'm using only 2 of the decision columns, each with 2 choices within. So in terms of combinations there are only 4 valid results, shown in the other picture, but against each of these results is the 5 total scores. I will then use these values to rank which combinations are most desirable. I'm sure this can be done with the same code you have, though by adding some more columns in the array so while building the combination, it's also building the associated score for that combination. I just don't see how to integrate it into your array formula...