Generate all possible combinations

sekhar03

New Member
Joined
Jul 26, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I've players from 2 teams 11 a side and want to generate all possible combinations. 22C11 with available players. Am using below sheet and VBA code to generate.
The VBA code is running very long (4 to 6 hours). I think this is due to multiple for loops. Is tehre any betetr way to achieve this ?

GRAND LEAGUE TEAMS GENERATOR_test.xlsm
ABCDEFGHIJKLMNOPQR
1Player
2Rohit Sharma705432Player1Player2Player3Player4Player5Player6Player7Player8Player9Player10Player11CreditsStatus
3KL RahulT1#N/A#N/A
4KohliT2#N/A#N/A
5Rishabh PantT3#N/A#N/A
6DhoniT4#N/A#N/A
7H PandyaT5#N/A#N/A
8Dinesh KarthikT6#N/A#N/A
9R JadejaT7#N/A#N/A
10BhuvaneswarT8#N/A#N/A
11K YadavT9#N/A#N/A
12BumrahT10#N/A#N/A
13KarunaratneT11#N/A#N/A
14Kusal PereraT12#N/A#N/A
15A FernandoT13#N/A#N/A
16Kusal MendisT14#N/A#N/A
17MathewsT15#N/A#N/A
18ThirmanneT16#N/A#N/A
19D DeSilvaT17#N/A#N/A
20Thisara PereraT18#N/A#N/A
21UdanaT19#N/A#N/A
22RajithaT20#N/A#N/A
23MalingaT21#N/A#N/A
Sheet5
Cell Formulas
RangeFormula
D2D2=COMBIN(22,11)
Q3:Q23Q3=VLOOKUP(F3,'Master Sheet'!$B$3:$F$24,4,FALSE)+VLOOKUP(G3,'Master Sheet'!$B$3:$F$24,4,FALSE)+VLOOKUP(H3,'Master Sheet'!$B$3:$F$24,4,FALSE)+VLOOKUP(I3,'Master Sheet'!$B$3:$F$24,4,FALSE)+VLOOKUP(J3,'Master Sheet'!$B$3:$F$24,4,FALSE)+VLOOKUP(L3,'Master Sheet'!$B$3:$F$24,4,FALSE)+VLOOKUP(M3,'Master Sheet'!$B$3:$F$24,4,FALSE)+VLOOKUP(N3,'Master Sheet'!$B$3:$F$24,4,FALSE)+VLOOKUP(O3,'Master Sheet'!$B$3:$F$24,4,FALSE)+VLOOKUP(P3,'Master Sheet'!$B$3:$F$24,4,FALSE)+VLOOKUP(K3,'Master Sheet'!$B$3:$F$24,4,FALSE)
R3:R23R3=IF(Q3>100,"INVALID","VALID")



VBA Code:

Public Sub GetCombinations_All()

Dim thisRow As Long

thisRow = 3

For i = 2 To 3
For j = i + 1 To 13
For k = j + 1 To 14
For l = k + 1 To 15
For m = l + 1 To 16
For n = m + 1 To 17
For o = n + 1 To 18
For p = o + 1 To 19
For q = p + 1 To 20
For r = q + 1 To 21
For s = r + 1 To 22

Cells(thisRow, 6).Value = Cells(i, 1).Value
Cells(thisRow, 7).Value = Cells(j, 1).Value
Cells(thisRow, 8).Value = Cells(k, 1).Value
Cells(thisRow, 9).Value = Cells(l, 1).Value
Cells(thisRow, 10).Value = Cells(m, 1).Value
Cells(thisRow, 11).Value = Cells(n, 1).Value
Cells(thisRow, 12).Value = Cells(o, 1).Value
Cells(thisRow, 13).Value = Cells(p, 1).Value
Cells(thisRow, 14).Value = Cells(q, 1).Value
Cells(thisRow, 15).Value = Cells(r, 1).Value
Cells(thisRow, 16).Value = Cells(s, 1).Value

thisRow = thisRow + 1
Next s
Next r
Next q
Next p
Next o
Next n
Next m
Next l
Next k
Next j
Next i

End Sub
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Phuoc

Active Member
Joined
Apr 29, 2016
Messages
313
Try this:

Code:
Public Sub GetCombinations_All_V2()
Dim i&, j&, k&, l&, m&, n&, o&, p&, q&, r&, s&
Dim thisRow As Long
Dim arrData, arrResult
Dim totalNum As Long
totalNum = Application.Combin(22, 11)

arrData = Range("A2:A23").Value
ReDim arrResult(1 To totalNum, 1 To 11)
For i = 1 To 12
For j = i + 1 To 13
For k = j + 1 To 14
For l = k + 1 To 15
For m = l + 1 To 16
For n = m + 1 To 17
For o = n + 1 To 18
For p = o + 1 To 19
For q = p + 1 To 20
For r = q + 1 To 21
For s = r + 1 To 22

thisRow = thisRow + 1
arrResult(thisRow, 1) = arrData(i, 1)
arrResult(thisRow, 2) = arrData(j, 1)
arrResult(thisRow, 3) = arrData(k, 1)
arrResult(thisRow, 4) = arrData(l, 1)
arrResult(thisRow, 5) = arrData(m, 1)
arrResult(thisRow, 6) = arrData(n, 1)
arrResult(thisRow, 7) = arrData(o, 1)
arrResult(thisRow, 8) = arrData(p, 1)
arrResult(thisRow, 9) = arrData(q, 1)
arrResult(thisRow, 10) = arrData(r, 1)
arrResult(thisRow, 11) = arrData(s, 1)

Next s
Next r
Next q
Next p
Next o
Next n
Next m
Next l
Next k
Next j
Next i
Range("F3").Resize(totalNum, 11).Value = arrResult
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,383
Messages
5,624,375
Members
416,025
Latest member
VN5968

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
Top