Generate all possible combinations

[sekhar03]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Player
2	Rohit Sharma			705432		Player1	Player2	Player3	Player4	Player5	Player6	Player7	Player8	Player9	Player10	Player11	Credits	Status
3	KL Rahul				T1												#N/A	#N/A
4	Kohli				T2												#N/A	#N/A
5	Rishabh Pant				T3												#N/A	#N/A
6	Dhoni				T4												#N/A	#N/A
7	H Pandya				T5												#N/A	#N/A
8	Dinesh Karthik				T6												#N/A	#N/A
9	R Jadeja				T7												#N/A	#N/A
10	Bhuvaneswar				T8												#N/A	#N/A
11	K Yadav				T9												#N/A	#N/A
12	Bumrah				T10												#N/A	#N/A
13	Karunaratne				T11												#N/A	#N/A
14	Kusal Perera				T12												#N/A	#N/A
15	A Fernando				T13												#N/A	#N/A
16	Kusal Mendis				T14												#N/A	#N/A
17	Mathews				T15												#N/A	#N/A
18	Thirmanne				T16												#N/A	#N/A
19	D DeSilva				T17												#N/A	#N/A
20	Thisara Perera				T18												#N/A	#N/A
21	Udana				T19												#N/A	#N/A
22	Rajitha				T20												#N/A	#N/A
23	Malinga				T21												#N/A	#N/A
Sheet5

Cell Formulas
Range	Formula
D2	D2	=COMBIN(22,11)
Q3:Q23	Q3	=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:R23	R3	=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

 

[Phuoc]

Try this:

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