Generate all possible combinations given a variable number of columns

450nick

Well-known Member
Joined
May 11, 2009
Messages
507
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:

VBA 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
 
Inside this loop is where the delimiters are added and can be controlled.
VBA Code:
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
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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?

VBA Code:
    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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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...
Capture.JPG
results.JPG
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top