Combinations / Permutations VBA

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,957
Office Version
  1. 365
Platform
  1. Windows
I know this has been covered, and believe me, I've looked around, but I haven't found a solution yet.

I am trying to figure out how to write a recursive function that will give every possible combination given a certain group size.

So, if the array is {1,2,3,4,5} and the group size is 3, I will get

1 2 3
1 2 4
1 2 5
1 3 4
1 3 5
1 4 5
2 3 4
2 3 5
2 4 5
3 4 5

And if I bump the group size to 4, I will get

1 2 3 4
1 2 3 5
1 2 4 5
1 3 4 5
2 3 4 5

I've tried to diagram out the sequence of events, as in table below, to try and figure out the code, but I've just been banging my head against the table.

So, if someone could help out and show me how this would work as a recursive VBA solution. Recursion has always made my head hurt.



<caption>
</caption><colgroup><col style="width: 54px"><col width="133"><col width="149"><col width="133"><col width="133"><col width="133"><col width="133"><col width="133"></colgroup><tbody>
[TH="bgcolor: #A6A6A6"] C [/TH]
[TH="bgcolor: #A6A6A6"] D [/TH]
[TH="bgcolor: #A6A6A6"] E [/TH]
[TH="bgcolor: #A6A6A6"] F [/TH]
[TH="bgcolor: #A6A6A6"] G [/TH]
[TH="bgcolor: #A6A6A6"] H [/TH]
[TH="bgcolor: #A6A6A6"] I [/TH]

[TH="bgcolor: #A6A6A6"] 1 [/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"] Results [/TD]

[TH="bgcolor: #A6A6A6"] 2 [/TH]
[TD="bgcolor: #FFFF00"] 1 [/TD]
[TD="bgcolor: #B4C6E7"] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1, 7 [/TD]

[TH="bgcolor: #A6A6A6"] 3 [/TH]
[TD="bgcolor: #FFFF00"] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7 [/TD]
[TD="bgcolor: #B4C6E7"] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1, 16 [/TD]

[TH="bgcolor: #A6A6A6"] 4 [/TH]
[TD="bgcolor: #FFFF00"] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16 [/TD]
[TD="bgcolor: #B4C6E7"] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1, 28 [/TD]

[TH="bgcolor: #A6A6A6"] 5 [/TH]
[TD="bgcolor: #FFFF00"] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28 [/TD]
[TD="bgcolor: #B4C6E7"] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1, 3 [/TD]

[TH="bgcolor: #A6A6A6"] 6 [/TH]
[TD="bgcolor: #FFFF00"] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3 [/TD]
[TD="bgcolor: #B4C6E7"] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1, 19 [/TD]

[TH="bgcolor: #A6A6A6"] 7 [/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1 [/TD]
[TD="bgcolor: #FFFF00"] 7 [/TD]
[TD="bgcolor: #B4C6E7"] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7, 16 [/TD]

[TH="bgcolor: #A6A6A6"] 8 [/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1 [/TD]
[TD="bgcolor: #FFFF00"] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16 [/TD]
[TD="bgcolor: #B4C6E7"] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7, 28 [/TD]

[TH="bgcolor: #A6A6A6"] 9 [/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1 [/TD]
[TD="bgcolor: #FFFF00"] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28 [/TD]
[TD="bgcolor: #B4C6E7"] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7, 3 [/TD]

[TH="bgcolor: #A6A6A6"] 10 [/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1 [/TD]
[TD="bgcolor: #FFFF00"] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3 [/TD]
[TD="bgcolor: #B4C6E7"] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7, 19 [/TD]

[TH="bgcolor: #A6A6A6"] 11 [/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7 [/TD]
[TD="bgcolor: #FFFF00"] 16 [/TD]
[TD="bgcolor: #B4C6E7"] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16, 28 [/TD]

[TH="bgcolor: #A6A6A6"] 12 [/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7 [/TD]
[TD="bgcolor: #FFFF00"] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28 [/TD]
[TD="bgcolor: #B4C6E7"] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16, 3 [/TD]

[TH="bgcolor: #A6A6A6"] 13 [/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7 [/TD]
[TD="bgcolor: #FFFF00"] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3 [/TD]
[TD="bgcolor: #B4C6E7"] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16, 19 [/TD]

[TH="bgcolor: #A6A6A6"] 14 [/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16 [/TD]
[TD="bgcolor: #FFFF00"] 28 [/TD]
[TD="bgcolor: #B4C6E7"] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28, 3 [/TD]

[TH="bgcolor: #A6A6A6"] 15 [/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16 [/TD]
[TD="bgcolor: #FFFF00"] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3 [/TD]
[TD="bgcolor: #B4C6E7"] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28, 19 [/TD]

[TH="bgcolor: #A6A6A6"] 16 [/TH]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28 [/TD]
[TD="bgcolor: #FFFF00"] 3 [/TD]
[TD="bgcolor: #B4C6E7"] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3, 19 [/TD]

[TH="bgcolor: #A6A6A6"] 17 [/TH]
[TD="bgcolor: #FFFF00"] 1 [/TD]
[TD="bgcolor: #B4C6E7"] 7 [/TD]
[TD="bgcolor: #C6E0B4"] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1, 7, 16 [/TD]

[TH="bgcolor: #A6A6A6"] 18 [/TH]
[TD="bgcolor: #FFFF00"] 1 [/TD]
[TD="bgcolor: #B4C6E7"] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16 [/TD]
[TD="bgcolor: #C6E0B4"] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1, 7, 28 [/TD]

[TH="bgcolor: #A6A6A6"] 19 [/TH]
[TD="bgcolor: #FFFF00"] 1 [/TD]
[TD="bgcolor: #B4C6E7"] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28 [/TD]
[TD="bgcolor: #C6E0B4"] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1, 7, 3 [/TD]

[TH="bgcolor: #A6A6A6"] 20 [/TH]
[TD="bgcolor: #FFFF00"] 1 [/TD]
[TD="bgcolor: #B4C6E7"] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3 [/TD]
[TD="bgcolor: #C6E0B4"] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1, 7, 19 [/TD]

[TH="bgcolor: #A6A6A6"] 21 [/TH]
[TD="bgcolor: #FFFF00"] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7 [/TD]
[TD="bgcolor: #B4C6E7"] 16 [/TD]
[TD="bgcolor: #C6E0B4"] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1, 16, 28 [/TD]

[TH="bgcolor: #A6A6A6"] 22 [/TH]
[TD="bgcolor: #FFFF00"] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7 [/TD]
[TD="bgcolor: #B4C6E7"] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28 [/TD]
[TD="bgcolor: #C6E0B4"] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1, 16, 3 [/TD]

[TH="bgcolor: #A6A6A6"] 23 [/TH]
[TD="bgcolor: #FFFF00"] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7 [/TD]
[TD="bgcolor: #B4C6E7"] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3 [/TD]
[TD="bgcolor: #C6E0B4"] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1, 16, 19 [/TD]

[TH="bgcolor: #A6A6A6"] 24 [/TH]
[TD="bgcolor: #FFFF00"] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16 [/TD]
[TD="bgcolor: #B4C6E7"] 28 [/TD]
[TD="bgcolor: #C6E0B4"] 3 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1, 28, 3 [/TD]

[TH="bgcolor: #A6A6A6"] 25 [/TH]
[TD="bgcolor: #FFFF00"] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16 [/TD]
[TD="bgcolor: #B4C6E7"] 28 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 3 [/TD]
[TD="bgcolor: #C6E0B4"] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1, 28, 19 [/TD]

[TH="bgcolor: #A6A6A6"] 26 [/TH]
[TD="bgcolor: #FFFF00"] 1 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 7 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 16 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 28 [/TD]
[TD="bgcolor: #B4C6E7"] 3 [/TD]
[TD="bgcolor: #C6E0B4"] 19 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "] 1, 3, 19 [/TD]

</tbody>
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
You seem to really be interested in this question. I found a thread you started over 2 years ago with the same question.

https://www.mrexcel.com/forum/excel-questions/907809-recursive-vba-combinations.html

In any event, here's one way to do it:

Code:
Sub CallRecur()
Dim keys()


    Cells.ClearContents
    myitems = Array(1, 2, 3, 4, 5)
    groupsize = 3
    
    ReDim keys(0 To groupsize)
    keys(0) = 0
    
    Call Recur(myitems, UBound(myitems) + 1, groupsize, 0, keys)
    
    
End Sub


Sub Recur(ByVal MyArray, ByVal n, ByVal gs, ByVal d, ByVal keys)


    
    For i = keys(d) + 1 To n
        keys(d + 1) = i
        If d + 1 = gs Then
            r = Cells(Rows.Count, "A").End(xlUp).Row + 1
            For j = 1 To gs
                Cells(r, j) = MyArray(keys(j) - 1)
            Next j
        Else
            Call Recur(MyArray, n, gs, d + 1, keys)
        End If
    Next i


End Sub

Obviously, you can populate the array in many different ways, and you can print out the results in many different ways. Note that when defining the array as I did, the lowest index is 0, which is why there's some +1/-1 in the code. Since I'm just printing the results out on a sheet, this will error out when you hit the maximum number of rows per sheet. But I suspect you'll run out of stack space long before that.

Hope this gives you some ideas though.
 
Upvote 0
Same basic algorithm, but I used a couple of public variables to clean up the code, which should be easier to follow now. I also only write the results to the worksheet once, which should make it faster. Note the "" entry at the start of the array.

Code:
Public Results As Object
Public MyItems As Variant

Sub CallRecur()

    Set Results = CreateObject("Scripting.Dictionary")
    MyItems = Array("", "dog", "cat", "horse", "iguana", "parakeet")
    
    Call Recur(UBound(MyItems), 3, 0, 1, "")
    
    Cells.ClearContents
    Range("A1").Resize(Results.Count).Value = WorksheetFunction.Transpose(Results.keys)
    Range("A:A").TextToColumns OtherChar:="|"
    
End Sub

' n is the number of items, gs is group size, d is depth,
' curix is the current index, comb is the combination so far
Sub Recur(ByRef n, ByRef gs, ByVal d, ByVal curix, ByVal comb)
Dim i As Long, comb2 As String

    For i = curix To n
        comb2 = comb & MyItems(i) & "|"
        If d + 1 = gs Then
            Results.Add comb2, 1
        Else
            Call Recur(n, gs, d + 1, i + 1, comb2)
        End If
    Next i

End Sub
 
Last edited:
Upvote 0
Thank you so much! That old post you mentioned, I tried adapting some old code from that project, but it worked a little differently and I couldn't get it to work.

Stepping through your code, I realize that I was getting close to to the answer, but not quite.

I'll study this and try to figure out how it works.

Thanks again, this has been driving me crazy.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

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