Excel VBA list pairing

I have 3 columns

apple     goat   sam
orange   dog   dave
pear        cat    matt

I need 3 columns of every combination (3x3x3 rows)

apple goat sam
apple goat dave
apple goat matt
apple dog sam
apple dog dave
apple dog matt
orange goat sam
orange dog dave
orange cat matt

I'm sure this type of problem has a name, but I don't know it. sorry for the silly example
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
What you are trying to generate are typically called "combinations" and "permutations".  Below is an example of a small VBA routine that can generate what you want.  Hopefully this will give you an idsea how to approach this.

Sub GenerateCombinations()

    Dim InArr1() As Variant
    Dim InArr2() As Variant
    Dim InArr3() As Variant
    Dim OutArr() As Variant
    Dim InNdx1, InNdx2, InNdx3, OutNdx As Long
    Dim InRng1 As Range
    Dim InRng2 As Range
    Dim InRng3 As Range
    Dim OutRng As Range
    Set InRng1 = Range("A1", Range("A1").End(xlDown))
    Set InRng2 = Range("B1", Range("B1").End(xlDown))
    Set InRng3 = Range("C1", Range("C1").End(xlDown))
    InArr1 = InRng1
    InArr2 = InRng2
    InArr3 = InRng3
    Set OutRng = Range("E1", Range("G1").Offset(UBound(InArr1) * UBound(InArr2) * UBound(InArr3)))
    OutArr = OutRng
    InNdx1 = 1
    InNdx2 = 1
    InNdx3 = 1
    OutNdx = 1
    For InNdx1 = 1 To UBound(InArr1)
        For InNdx2 = 1 To UBound(InArr2)
            For InNdx3 = 1 To UBound(InArr3)
                OutArr(OutNdx, 1) = InArr1(InNdx1, 1)
                OutArr(OutNdx, 2) = InArr2(InNdx2, 1)
                OutArr(OutNdx, 3) = InArr3(InNdx3, 1)
                OutNdx = OutNdx + 1
    OutRng.Value = OutArr

End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
newparadigmzAuthor Commented:

Bill PrewIT / Software Engineering ConsultantCommented:
Welcome, glad that helped.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.