# Generate all possible combinations

#### sekhar03

##### New Member
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
10BhuvaneswarT8#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

### 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
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``````

Replies
0
Views
46
Replies
3
Views
102
Replies
2
Views
73
Replies
3
Views
137
Replies
6
Views
77

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.

### Which adblocker are you using?

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

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