Excel – how to create list with all possible combinations

microsoft excelvba

I have the following table:

a b c d ...
q w e r ...
z x   v ...
  p

I am having lots of trouble finding an algorithm (preferably VBA but the thinking is almost the same in other languages) that will generate a list with all combinations – besides the fact that I only have a few rows, there are a lots of columns and this would not be accurate if done manual, I trust VBA to make it 100% complete.

so, the output should be a list like this:

a
a,q
a,z
q,z
z
a/b
a/b,w
a/b,x
a/b,p
a/b,w,x
a/b,w,p
a/b,x,p
a/w
a/w,x
a/w,p
a/x
a/x,p
a/p
a,q/b
a,q/b,w
a,q/b,x
a,q/b,p
a,q/b,w,x
a,q/b,w,p
a,q/b,x,p
a,q/w
a,q/w,x
a,q/w,p
a,q/x
a,q/x,p
a,q/p
....etc.

  • I do not really care about the "/" and "," signs, I will find a way to put them right ("/" is between elements of separate columns while "," is between the elements from the same column)

  • the combinations are made two ways – horizontally and vertically with the following restriction: can only combine 'n-1' elements (horizontally and/or vertically)

Best Answer

Your example indicated 12 items. This code (supplied by John Coleman in 2005) will list the 4095 permutations of the list in column B. There are 2N-1 items:

Sub MAIN()
    B = Array("a", "b", "c", "d", "q", "w", "e", "r", "z", "x", "v", "p")
    Call GrayCode(B)
End Sub

Function GrayCode(Items As Variant) As String
    Dim CodeVector() As Integer
    Dim i, kk As Integer
    Dim lower As Integer, upper As Integer
    Dim SubList As String
    Dim NewSub As String
    Dim done As Boolean
    Dim OddStep As Boolean

    kk = 1
    OddStep = True
    lower = LBound(Items)
    upper = UBound(Items)

    ReDim CodeVector(lower To upper) 'it starts all 0
    Do Until done
        'Add a new subset according to current contents
        'of CodeVector

        NewSub = ""
        For i = lower To upper
            If CodeVector(i) = 1 Then
                If NewSub = "" Then
                    NewSub = "," & Items(i)
                Else
                    NewSub = NewSub & "," & Items(i)
                End If
            End If
        Next i
        If NewSub = "" Then NewSub = "{}" 'empty set
        SubList = SubList & vbCrLf & NewSub
        Cells(kk, 2) = Mid(NewSub, 2)
        kk = kk + 1
        'now update code vector
        If OddStep Then
            'just flip first bit
            CodeVector(lower) = 1 - CodeVector(lower)
        Else
            'first locate first 1
            i = lower
            Do While CodeVector(i) <> 1
                i = i + 1
            Loop
            'done if i = upper:
            If i = upper Then
                done = True
            Else
                'if not done then flip the *next* bit:
                i = i + 1
                CodeVector(i) = 1 - CodeVector(i)
            End If
        End If
        OddStep = Not OddStep 'toggles between even and odd steps
    Loop
    GrayCode = SubList
End Function

enter image description here

Reference:

John Coleman Code

You can change/add/remove items by changing the Array(). Too many will overflow the limits to the number of items in a colummn.

Related Question