Excel for combinations

Question

I have 2 columns as follows:

Column 1: A B C D E F G H I J

Column 2: 1 2 3 4 5 6 . . . 31

I need to create 6 groups of 5 each with a max of 3 rows from Column 2, making sure that each row in Column 2 is not repeating with the rows in Column 1 on a group. Could this be done only using Excel functions? Or would this require some amount of coding?

It isn't clear what you are asking (e.g. what is a group? Is it a string consisting of letters and numbers?). It doesn't seem that there would be a simple spreadsheet formula approach but it is a strictly finite problem so in principle you could do it. Using the solver for what seems to be a constraint satisfaction problem might be possible. Or, implement an algorithm for solving it in VBA. — John Coleman, Apr 23 '20 at 01:04

score -1 · Answer 1 · answered Apr 23 '20 at 01:24

I think you are talking about some kind of combination code, or perhaps permutation code. It's hard to tell what you want, based only on your description/comments. Maybe you can test this and see if it works.

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:B1").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

All credit to Tim Williams!!

I found the code here:

VBA - Write all possible combinations of 4 columns of data

That's just a long shot. I really don't know what you want. Of course you can Google 'excel vba combinations' or 'excel vba permutations' and see what you can come up with!

Excel for combinations

1 Answers1