# Need Vba codes for the following

#### Rajeev Muraleedharan

##### New Member
i need vba codes for the below project. i have a list of 22 players from 2 teams like

A B C
Randy AUS BAT
Bryan NZ WK
John AUS BOWL
Daniel NZ ALL
Kannan AUS ALL
Shyam AUS BAT
Paul NZ BOWL

etc...

i need all possible combination of 11 members from this 22 members according to the below conditions.

the conditions are:
1. WK - minimum 1 and max 4 members
2. BAT - minimum 3 and max 6 members
3. ALL - minimum1 and max 4 members
4. BOWL - minimum 3 and max 6 members

Can anyone help on this?

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### StephenCrump

##### MrExcel MVP
Welcome to the Forum!

Combin(22,11) is small enough to manage in Excel, so we can take a lazy approach, simply generating all possible combinations and filtering the valid ones.

Using the code below, we can generate all 705,4342 combinations in A4:K:705435.

For the sample player set in R4:S25, we can test valid combinations using the formulae in columns L:P. There are 395,920 valid combinations, distributed as follows:

ABCDE
14747
2WKBATALLBOWLCOMBs
343134,900
4341319,600
5332329,400
6331419,600
7251317,640
8242344,100
9241429,400
10233329,400
11232444,100
12231517,640
1316133,920
14152317,640
15151411,760
16143319,600
17142429,400
18141511,760
1913434,900
20133419,600
21132517,640
2213163,920
23
24395,920
1
Cell Formulas
RangeFormula
E3:E22E3=COMBIN(A\$1,A3)*COMBIN(B\$1,B3)*COMBIN(C\$1,C3)*COMBIN(D\$1,D3)
E24E24=SUM(E3:E22)

Depending on the mix of players, this total may vary between approx. 300,000 and 400,000.

ABCDEFGHIJKLMNOPQRS
1Min1313
2Max4646
3COMBINSWKBATALLBOWLValid?Players
412345678910114700FALSE1WK
512345678910124610FALSE2WK
612345678910134610FALSE3WK
712345678910144610FALSE4WK
812345678910154610FALSE5BAT
912345678910194600FALSE6BAT
1012345678910204600FALSE7BAT
1112345678910214600FALSE8BAT
1212345678910224600FALSE9BAT
1312345678911144610FALSE10BAT
1412345678911154610FALSE11BAT
1512345678911194600FALSE12ALL
1612345678911204600FALSE13ALL
1712345678911214600FALSE14ALL
1812345678911224600FALSE15ALL
1912345678912194511FALSE16BOWL
2012345678912204511FALSE17BOWL
2112345678912214511FALSE18BOWL
2212345678912224511FALSE19BOWL
2312345678913194511FALSE20BOWL
2412345678913204511FALSE21BOWL
2512345678913214511FALSE22BOWL
2612345678913224511FALSE
2712345678914164511FALSE
1
Cell Formulas
RangeFormula
L4:O27L4=IFERROR(SUMPRODUCT(--(LOOKUP(A4:K4,\$R\$4:\$R\$25,\$S\$4:\$S\$25)=L\$3)),0)
P4:P27P4=AND((L4:O4>=L\$1:O\$1)*(L4:O4<=L\$2:O\$2))

VBA Code:
``````Sub Test()

Dim lResults() As Long

lResults = GetCombinations(22, 11)
Range("A4").Resize(UBound(lResults), UBound(lResults, 2)).Value = lResults

End Sub
Function GetCombinations(lNumber As Long, lNoChosen As Long) As Long()

Dim lOutput() As Long, lCombinations As Long
Dim i As Long, j As Long, k As Long

lCombinations = WorksheetFunction.Combin(lNumber, lNoChosen)
ReDim lOutput(1 To lCombinations, 1 To lNoChosen)

For i = 1 To lNoChosen
lOutput(1, i) = i
Next i

For i = 2 To lCombinations
For j = 1 To lNoChosen
lOutput(i, j) = lOutput(i - 1, j)
Next j
For j = lNoChosen To 1 Step -1
lOutput(i, j) = lOutput(i, j) + 1
If lOutput(i, j) <= lNumber - (lNoChosen - j) Then Exit For
Next j
For k = j + 1 To lNoChosen
lOutput(i, k) = lOutput(i, k - 1) + 1
Next k
Next i

GetCombinations = lOutput

End Function``````

#### Rajeev Muraleedharan

##### New Member
Thank you sir, adding to the above i need the below conditions also.
There should be max of 7 players from one team and minimum of 4 players. Plus if i add credits in column d.. for example 9.5, 8.5 etc.. i need only those 11 players whose sum credits is 100. I hope this can give lesser results.

I sincerely thank you in advamce for your help.

#### StephenCrump

##### MrExcel MVP
Additional formulae in columns Q and R are similar to those already in L:O (which I have corrected slightly since the previous post).

You'll need to filter AUS = 4,5,6,7 and I'm guessing credit <=100 (i.e. your team budget is 100 credits?). For the sample dataset shown, this produces ~190,000 combinations

ABCDEFGHIJKLMNOPQRSTUVW
1Min1313
2Max4646
3COMBINSWKBATALLBOWLValid?AUSCreditsPlayersPositionTeamCredits
412345678910114700FALSE6103.51WKAUS8.5
512345678910124610FALSE7100.52WKAUS5.5
612345678910134610FALSE7107.53WKNZ4.5
712345678910144610FALSE6103.54WKNZ9.5
812345678910154610FALSE699.55BATAUS13.5
912345678910164601FALSE7101.56BATAUS6.5
1012345678910174601FALSE7104.57BATAUS13.5
1112345678910184601FALSE7109.58BATAUS12.5
1212345678910194601FALSE6102.59BATNZ14.5
1312345678910204601FALSE6100.510BATNZ6.5
1412345678910214601FALSE6109.511BATNZ8.5
1512345678910224601FALSE699.512ALLAUS5.5
1612345678911124610FALSE7102.513ALLAUS12.5
1712345678911134610FALSE7109.514ALLNZ8.5
1812345678911144610FALSE6105.515ALLNZ4.5
1912345678911154610FALSE6101.516BOWLAUS6.5
2012345678911164601FALSE7103.517BOWLAUS9.5
2112345678911174601FALSE7106.518BOWLAUS14.5
2212345678911184601FALSE7111.519BOWLNZ7.5
2312345678911194601FALSE6104.520BOWLNZ5.5
2412345678911204601FALSE6102.521BOWLNZ14.5
2512345678911214601FALSE6111.522BOWLNZ4.5
2612345678911224601FALSE6101.5
2712345678912134520FALSE8106.5
Sheet1
Cell Formulas
RangeFormula
L4:O25L4=IFERROR(SUMPRODUCT(--(LOOKUP(\$A4:\$K4,\$T\$4:\$T\$25,\$U\$4:\$U\$25)=L\$3)),0)
P4:P25P4=AND((L4:O4>=L\$1:O\$1)*(L4:O4<=L\$2:O\$2))
Q4:Q27Q4=IFERROR(SUMPRODUCT(--(LOOKUP(\$A4:\$K4,\$T\$4:\$T\$25,\$V\$4:\$V\$25)=Q\$3)),0)
R4:R27R4=IFERROR(SUMPRODUCT(LOOKUP(\$A4:\$K4,\$T\$4:\$T\$25,\$W\$4:\$W\$25)),0)

#### Rajeev Muraleedharan

##### New Member

Yes, budget is 100 credits...and instead of numbers i need player names there

#### StephenCrump

##### MrExcel MVP
I have kept it numeric so far, because it's far more efficient than working with strings.

Once you have your optimal team(s), a simple =INDEX(YourPlayerList,N) will convert each N to a player name.

#### Rajeev Muraleedharan

##### New Member

Can you help me how to do it? i.e. convert numbers into characters since iam not wellversed in formulas as well as vba..

#### Rajeev Muraleedharan

##### New Member
Is there any changes in vba codes??

#### StephenCrump

##### MrExcel MVP
Can you help me how to do it? i.e. convert numbers into characters since iam not wellversed in formulas as well as vba..

Look up Excel's INDEX() function. It's very easy to use.

Is there any changes in vba codes??

No changes to the VBA code. Have you tried it yet?

#### Dannydev

##### New Member
Welcome to the Forum!

Combin(22,11) is small enough to manage in Excel, so we can take a lazy approach, simply generating all possible combinations and filtering the valid ones.

Using the code below, we can generate all 705,4342 combinations in A4:K:705435.

For the sample player set in R4:S25, we can test valid combinations using the formulae in columns L:P. There are 395,920 valid combinations, distributed as follows:

ABCDE
14747
2WKBATALLBOWLCOMBs
343134,900
4341319,600
5332329,400
6331419,600
7251317,640
8242344,100
9241429,400
10233329,400
11232444,100
12231517,640
1316133,920
14152317,640
15151411,760
16143319,600
17142429,400
18141511,760
1913434,900
20133419,600
21132517,640
2213163,920
23
24395,920
1
Cell Formulas
RangeFormula
E3:E22E3=COMBIN(A\$1,A3)*COMBIN(B\$1,B3)*COMBIN(C\$1,C3)*COMBIN(D\$1,D3)
E24E24=SUM(E3:E22)

Depending on the mix of players, this total may vary between approx. 300,000 and 400,000.

ABCDEFGHIJKLMNOPQRS
1Min1313
2Max4646
3COMBINSWKBATALLBOWLValid?Players
412345678910114700FALSE1WK
512345678910124610FALSE2WK
612345678910134610FALSE3WK
712345678910144610FALSE4WK
812345678910154610FALSE5BAT
912345678910194600FALSE6BAT
1012345678910204600FALSE7BAT
1112345678910214600FALSE8BAT
1212345678910224600FALSE9BAT
1312345678911144610FALSE10BAT
1412345678911154610FALSE11BAT
1512345678911194600FALSE12ALL
1612345678911204600FALSE13ALL
1712345678911214600FALSE14ALL
1812345678911224600FALSE15ALL
1912345678912194511FALSE16BOWL
2012345678912204511FALSE17BOWL
2112345678912214511FALSE18BOWL
2212345678912224511FALSE19BOWL
2312345678913194511FALSE20BOWL
2412345678913204511FALSE21BOWL
2512345678913214511FALSE22BOWL
2612345678913224511FALSE
2712345678914164511FALSE
1
Cell Formulas
RangeFormula
L4:O27L4=IFERROR(SUMPRODUCT(--(LOOKUP(A4:K4,\$R\$4:\$R\$25,\$S\$4:\$S\$25)=L\$3)),0)
P4:P27P4=AND((L4:O4>=L\$1:O\$1)*(L4:O4<=L\$2:O\$2))

VBA Code:
``````Sub Test()

Dim lResults() As Long

lResults = GetCombinations(22, 11)
Range("A4").Resize(UBound(lResults), UBound(lResults, 2)).Value = lResults

End Sub
Function GetCombinations(lNumber As Long, lNoChosen As Long) As Long()

Dim lOutput() As Long, lCombinations As Long
Dim i As Long, j As Long, k As Long

lCombinations = WorksheetFunction.Combin(lNumber, lNoChosen)
ReDim lOutput(1 To lCombinations, 1 To lNoChosen)

For i = 1 To lNoChosen
lOutput(1, i) = i
Next i

For i = 2 To lCombinations
For j = 1 To lNoChosen
lOutput(i, j) = lOutput(i - 1, j)
Next j
For j = lNoChosen To 1 Step -1
lOutput(i, j) = lOutput(i, j) + 1
If lOutput(i, j) <= lNumber - (lNoChosen - j) Then Exit For
Next j
For k = j + 1 To lNoChosen
lOutput(i, k) = lOutput(i, k - 1) + 1
Next k
Next i

GetCombinations = lOutput

End Function``````
I have reproduced the sheet exactly as above, however the formula =AND((L5:O5>=L\$1:O\$1)*(L5:O5<=L\$2:O\$2)) returns #VAlue and not true or False. Is there something I haven't done right

Replies
2
Views
455
Replies
1
Views
8K
Replies
6
Views
93
Replies
1
Views
195
Replies
20
Views
375

1,127,832
Messages
5,627,152
Members
416,224
Latest member
RichardHell

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

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