Recursive VBA combinations

[lrobbo314]

I have the data below. The data is in E1:J15.

I need to get all possible combinations of the names in the table.

But, the number of names from each column must correspond with the numbers in row 1.

So the first result would be

Andy, Doug, Eddie, Allen, Calvin, Eric, Greg, Graham, Bill

Then it would cycle through all the P6 entries. So the next one would be

Andy, Doug, Eddie, Allen, Calvin, Eric, Greg, Graham, Miguel

then

Andy, Doug, Eddie, Allen, Calvin, Eric, Greg, Graham, Tristan and so on until all possible combinations were listed.

I

1	2	3	1	1	1
P1	P2	P3	P4	P5	P6
Andy	Doug	Allen	Greg	Graham	Bill
Tom	Eddie	Calvin	Tyler	Mason	Sanchez
Ryan	Frank	Eric	Gary	Robbie	Miguel
Ben	Jeremy	Randall	Travis	Adam	Tristan
Matthew	Jonathan	Vincent	Austin	Randy	Morgan
Johnny	Mark	Travis	Scott	Dustin	TJ
Philip	Matt	Steve	Richard	Dan	Kyle
Brian	Shane	Jordan	Charles	Phil	Mike
Derek	Thomas	Rueben	Jason	Matt	Leroy
Sam		Willie		Andrew
		Nate		Sebastian
				Josh
				Caleb

I have the following recursive code from an old project

Sub Combo()
Dim vArray() As Variant, vResult() As Variant
Dim lRow As Long
Dim r As Range

Set r = Range("A1:A3")

For i = 0 To r.Rows.Count - 1
    ReDim Preserve vArray(0 To i)
    vArray(i) = r(i + 1)
Next

vResult = vArray

perm1 vArray, vResult, 0, lRow

End Sub

Sub perm1(vArray As Variant, vResult As Variant, lInd As Long, lRow As Long)
Dim j As Long
Dim res As Double

For j = 0 To UBound(vArray)
    vResult(lInd) = vArray(j)
    If lInd = UBound(vArray) Then
        lRow = lRow + 1
        Range("J" & lRow).Resize(1, UBound(vArray) - LBound(vArray) + 1).Value = vResult
    Else
        perm1 vArray, vResult, lInd + 1, lRow
    End If
 Next j
End Sub

But it deals with just 1 single dimension array.

Honestly I am not very familiar with recursive programming and adjusting the code for these new constraints is a bit above my head.

Any help would be greatly appreciated. Thanks.

 

[rpaulson]

note sure this is what you need.

when I ran it it stopped after 65,000 cominations

Sub do_it()
Application.ScreenUpdating = False
wr = 1

For e = 3 To Cells(Rows.Count, "E").End(xlUp).Row
For f = 3 To Cells(Rows.Count, "F").End(xlUp).Row
For g = 3 To Cells(Rows.Count, "G").End(xlUp).Row
For h = 3 To Cells(Rows.Count, "H").End(xlUp).Row
For i = 3 To Cells(Rows.Count, "I").End(xlUp).Row
For j = 3 To Cells(Rows.Count, "J").End(xlUp).Row

data = Cells(e, "E") & ", " & Cells(f, "F") & ", " & Cells(g, "G") & ", " & Cells(h, "H") & ", " & Cells(i, "I") & ", " & Cells(j, "J")


Cells(wr, "A") = data
wr = wr + 1
Next
Next
Next
Next
Next
Next

Application.ScreenUpdating = True
End Sub

Ross

 

[lrobbo314]

Thanks for the reply, but this is not quite what I am looking for.

The numbers at the top indicate the # of people that need to be in each listing.

So, for the output that you have going into column A, there are only 5 names in each cell.

There should be 9.

1 from P1
2 from P2
3 from P3
1 from P4
1 from P5 and
1 from P6

So the first example was

Andy, Doug, Eddie, Allen, Calvin, Eric, Greg, Graham, Bill

and the second is just going on to the next person in P6, which would be Sanchez.

Then once we've gone through all the names in P6, it would move to the next person in P5, Mason, and revert to the 1st name in P6, Bill.

Let me know if this makes sense,

Thanks again for looking at this.

 

[Eric W]

What are you trying to accomplish with this? I believe I understand what you're asking, and I can design a macro to accomplish it, but I believe the results will be fairly useless. According to my calculations, the example above will generate 62,548,200 different combinations. That's roughly 60 times the maximum number of rows you can put on an Excel sheet. Assuming I came up with a way to store/print all the combinations, how will you sift through them all to find the ones you want?