Recursive VBA combinations

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,957
Office Version
  1. 365
Platform
  1. Windows
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

[table="width: 500, class: grid"]
[tr]
[td]1[/td]
[td]2[/td]
[td]3[/td]
[td]1[/td]
[td]1[/td]
[td]1[/td]
[/tr]
[tr]
[td]P1[/td]
[td]P2[/td]
[td]P3[/td]
[td]P4[/td]
[td]P5[/td]
[td]P6[/td]
[/tr]
[tr]
[td]Andy[/td]
[td]Doug[/td]
[td]Allen[/td]
[td]Greg[/td]
[td]Graham[/td]
[td]Bill[/td]
[/tr]
[tr]
[td]Tom[/td]
[td]Eddie[/td]
[td]Calvin[/td]
[td]Tyler[/td]
[td]Mason[/td]
[td]Sanchez[/td]


[/tr]
[tr]
[td]Ryan[/td]
[td]Frank[/td]
[td]Eric[/td]
[td]Gary[/td]
[td]Robbie[/td]
[td]Miguel[/td]

[/tr]
[tr]
[td]Ben[/td]
[td]Jeremy[/td]
[td]Randall[/td]
[td]Travis[/td]
[td]Adam[/td]
[td]Tristan[/td]

[/tr]
[tr]
[td]Matthew[/td]
[td]Jonathan[/td]
[td]Vincent[/td]
[td]Austin[/td]
[td]Randy[/td]
[td]Morgan[/td]

[/tr]
[tr]
[td]Johnny[/td]
[td]Mark[/td]
[td]Travis[/td]
[td]Scott[/td]
[td]Dustin[/td]
[td]TJ[/td]

[/tr]
[tr]
[td]Philip[/td]
[td]Matt[/td]
[td]Steve[/td]
[td]Richard[/td]
[td]Dan[/td]
[td]Kyle[/td]

[/tr]
[tr]
[td]Brian[/td]
[td]Shane[/td]
[td]Jordan[/td]
[td]Charles[/td]
[td]Phil[/td]
[td]Mike[/td]

[/tr]
[tr]
[td]Derek[/td]
[td]Thomas[/td]
[td]Rueben[/td]
[td]Jason[/td]
[td]Matt[/td]
[td]Leroy[/td]

[/tr]
[tr]
[td]Sam[/td]
[td][/td]
[td]Willie[/td]
[td][/td]
[td]Andrew[/td]
[td][/td]

[/tr]
[tr]
[td][/td]
[td][/td]
[td]Nate[/td]
[td][/td]
[td]Sebastian[/td]
[td][/td]

[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]Josh[/td]
[td][/td]

[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]Caleb[/td]
[td][/td]

[/tr]
[/table]

I have the following recursive code from an old project

Code:
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
note sure this is what you need.

when I ran it it stopped after 65,000 cominations

Code:
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,991
Messages
6,175,820
Members
452,672
Latest member
missbanana

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