Need Vba codes for the following

[Rajeev Muraleedharan]

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?

 

[StephenCrump]

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:

	A	B	C	D	E
1	4	7	4	7
2	WK	BAT	ALL	BOWL	COMBs
3	4	3	1	3	4,900
4	3	4	1	3	19,600
5	3	3	2	3	29,400
6	3	3	1	4	19,600
7	2	5	1	3	17,640
8	2	4	2	3	44,100
9	2	4	1	4	29,400
10	2	3	3	3	29,400
11	2	3	2	4	44,100
12	2	3	1	5	17,640
13	1	6	1	3	3,920
14	1	5	2	3	17,640
15	1	5	1	4	11,760
16	1	4	3	3	19,600
17	1	4	2	4	29,400
18	1	4	1	5	11,760
19	1	3	4	3	4,900
20	1	3	3	4	19,600
21	1	3	2	5	17,640
22	1	3	1	6	3,920
23
24					395,920
1

Cell Formulas
Range	Formula
E3:E22	E3	=COMBIN(A$1,A3)*COMBIN(B$1,B3)*COMBIN(C$1,C3)*COMBIN(D$1,D3)
E24	E24	=SUM(E3:E22)

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

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1											Min	1	3	1	3
2											Max	4	6	4	6
3	COMBINS											WK	BAT	ALL	BOWL	Valid?		Players
4	1	2	3	4	5	6	7	8	9	10	11	4	7	0	0	FALSE		1	WK
5	1	2	3	4	5	6	7	8	9	10	12	4	6	1	0	FALSE		2	WK
6	1	2	3	4	5	6	7	8	9	10	13	4	6	1	0	FALSE		3	WK
7	1	2	3	4	5	6	7	8	9	10	14	4	6	1	0	FALSE		4	WK
8	1	2	3	4	5	6	7	8	9	10	15	4	6	1	0	FALSE		5	BAT
9	1	2	3	4	5	6	7	8	9	10	19	4	6	0	0	FALSE		6	BAT
10	1	2	3	4	5	6	7	8	9	10	20	4	6	0	0	FALSE		7	BAT
11	1	2	3	4	5	6	7	8	9	10	21	4	6	0	0	FALSE		8	BAT
12	1	2	3	4	5	6	7	8	9	10	22	4	6	0	0	FALSE		9	BAT
13	1	2	3	4	5	6	7	8	9	11	14	4	6	1	0	FALSE		10	BAT
14	1	2	3	4	5	6	7	8	9	11	15	4	6	1	0	FALSE		11	BAT
15	1	2	3	4	5	6	7	8	9	11	19	4	6	0	0	FALSE		12	ALL
16	1	2	3	4	5	6	7	8	9	11	20	4	6	0	0	FALSE		13	ALL
17	1	2	3	4	5	6	7	8	9	11	21	4	6	0	0	FALSE		14	ALL
18	1	2	3	4	5	6	7	8	9	11	22	4	6	0	0	FALSE		15	ALL
19	1	2	3	4	5	6	7	8	9	12	19	4	5	1	1	FALSE		16	BOWL
20	1	2	3	4	5	6	7	8	9	12	20	4	5	1	1	FALSE		17	BOWL
21	1	2	3	4	5	6	7	8	9	12	21	4	5	1	1	FALSE		18	BOWL
22	1	2	3	4	5	6	7	8	9	12	22	4	5	1	1	FALSE		19	BOWL
23	1	2	3	4	5	6	7	8	9	13	19	4	5	1	1	FALSE		20	BOWL
24	1	2	3	4	5	6	7	8	9	13	20	4	5	1	1	FALSE		21	BOWL
25	1	2	3	4	5	6	7	8	9	13	21	4	5	1	1	FALSE		22	BOWL
26	1	2	3	4	5	6	7	8	9	13	22	4	5	1	1	FALSE
27	1	2	3	4	5	6	7	8	9	14	16	4	5	1	1	FALSE
1

Cell Formulas
Range	Formula
L4:O27	L4	=IFERROR(SUMPRODUCT(--(LOOKUP(A4:K4,$R$4:$R$25,$S$4:$S$25)=L$3)),0)
P4:P27	P4	=AND((L4:O4>=L$1:O$1)*(L4:O4<=L$2:O$2))

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]

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]

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

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1											Min	1	3	1	3
2											Max	4	6	4	6
3	COMBINS											WK	BAT	ALL	BOWL	Valid?	AUS	Credits		Players	Position	Team	Credits
4	1	2	3	4	5	6	7	8	9	10	11	4	7	0	0	FALSE	6	103.5		1	WK	AUS	8.5
5	1	2	3	4	5	6	7	8	9	10	12	4	6	1	0	FALSE	7	100.5		2	WK	AUS	5.5
6	1	2	3	4	5	6	7	8	9	10	13	4	6	1	0	FALSE	7	107.5		3	WK	NZ	4.5
7	1	2	3	4	5	6	7	8	9	10	14	4	6	1	0	FALSE	6	103.5		4	WK	NZ	9.5
8	1	2	3	4	5	6	7	8	9	10	15	4	6	1	0	FALSE	6	99.5		5	BAT	AUS	13.5
9	1	2	3	4	5	6	7	8	9	10	16	4	6	0	1	FALSE	7	101.5		6	BAT	AUS	6.5
10	1	2	3	4	5	6	7	8	9	10	17	4	6	0	1	FALSE	7	104.5		7	BAT	AUS	13.5
11	1	2	3	4	5	6	7	8	9	10	18	4	6	0	1	FALSE	7	109.5		8	BAT	AUS	12.5
12	1	2	3	4	5	6	7	8	9	10	19	4	6	0	1	FALSE	6	102.5		9	BAT	NZ	14.5
13	1	2	3	4	5	6	7	8	9	10	20	4	6	0	1	FALSE	6	100.5		10	BAT	NZ	6.5
14	1	2	3	4	5	6	7	8	9	10	21	4	6	0	1	FALSE	6	109.5		11	BAT	NZ	8.5
15	1	2	3	4	5	6	7	8	9	10	22	4	6	0	1	FALSE	6	99.5		12	ALL	AUS	5.5
16	1	2	3	4	5	6	7	8	9	11	12	4	6	1	0	FALSE	7	102.5		13	ALL	AUS	12.5
17	1	2	3	4	5	6	7	8	9	11	13	4	6	1	0	FALSE	7	109.5		14	ALL	NZ	8.5
18	1	2	3	4	5	6	7	8	9	11	14	4	6	1	0	FALSE	6	105.5		15	ALL	NZ	4.5
19	1	2	3	4	5	6	7	8	9	11	15	4	6	1	0	FALSE	6	101.5		16	BOWL	AUS	6.5
20	1	2	3	4	5	6	7	8	9	11	16	4	6	0	1	FALSE	7	103.5		17	BOWL	AUS	9.5
21	1	2	3	4	5	6	7	8	9	11	17	4	6	0	1	FALSE	7	106.5		18	BOWL	AUS	14.5
22	1	2	3	4	5	6	7	8	9	11	18	4	6	0	1	FALSE	7	111.5		19	BOWL	NZ	7.5
23	1	2	3	4	5	6	7	8	9	11	19	4	6	0	1	FALSE	6	104.5		20	BOWL	NZ	5.5
24	1	2	3	4	5	6	7	8	9	11	20	4	6	0	1	FALSE	6	102.5		21	BOWL	NZ	14.5
25	1	2	3	4	5	6	7	8	9	11	21	4	6	0	1	FALSE	6	111.5		22	BOWL	NZ	4.5
26	1	2	3	4	5	6	7	8	9	11	22	4	6	0	1	FALSE	6	101.5
27	1	2	3	4	5	6	7	8	9	12	13	4	5	2	0	FALSE	8	106.5
Sheet1

Cell Formulas
Range	Formula
L4:O25	L4	=IFERROR(SUMPRODUCT(--(LOOKUP($A4:$K4,$T$4:$T$25,$U$4:$U$25)=L$3)),0)
P4:P25	P4	=AND((L4:O4>=L$1:O$1)*(L4:O4<=L$2:O$2))
Q4:Q27	Q4	=IFERROR(SUMPRODUCT(--(LOOKUP($A4:$K4,$T$4:$T$25,$V$4:$V$25)=Q$3)),0)
R4:R27	R4	=IFERROR(SUMPRODUCT(LOOKUP($A4:$K4,$T$4:$T$25,$W$4:$W$25)),0)

 

[Rajeev Muraleedharan]

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

 

[StephenCrump]

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]

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]

Is there any changes in vba codes??

 

[StephenCrump]

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]

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:

	A	B	C	D	E
1	4	7	4	7
2	WK	BAT	ALL	BOWL	COMBs
3	4	3	1	3	4,900
4	3	4	1	3	19,600
5	3	3	2	3	29,400
6	3	3	1	4	19,600
7	2	5	1	3	17,640
8	2	4	2	3	44,100
9	2	4	1	4	29,400
10	2	3	3	3	29,400
11	2	3	2	4	44,100
12	2	3	1	5	17,640
13	1	6	1	3	3,920
14	1	5	2	3	17,640
15	1	5	1	4	11,760
16	1	4	3	3	19,600
17	1	4	2	4	29,400
18	1	4	1	5	11,760
19	1	3	4	3	4,900
20	1	3	3	4	19,600
21	1	3	2	5	17,640
22	1	3	1	6	3,920
23
24					395,920
1

Cell Formulas
Range	Formula
E3:E22	E3	=COMBIN(A$1,A3)*COMBIN(B$1,B3)*COMBIN(C$1,C3)*COMBIN(D$1,D3)
E24	E24	=SUM(E3:E22)

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

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1											Min	1	3	1	3
2											Max	4	6	4	6
3	COMBINS											WK	BAT	ALL	BOWL	Valid?		Players
4	1	2	3	4	5	6	7	8	9	10	11	4	7	0	0	FALSE		1	WK
5	1	2	3	4	5	6	7	8	9	10	12	4	6	1	0	FALSE		2	WK
6	1	2	3	4	5	6	7	8	9	10	13	4	6	1	0	FALSE		3	WK
7	1	2	3	4	5	6	7	8	9	10	14	4	6	1	0	FALSE		4	WK
8	1	2	3	4	5	6	7	8	9	10	15	4	6	1	0	FALSE		5	BAT
9	1	2	3	4	5	6	7	8	9	10	19	4	6	0	0	FALSE		6	BAT
10	1	2	3	4	5	6	7	8	9	10	20	4	6	0	0	FALSE		7	BAT
11	1	2	3	4	5	6	7	8	9	10	21	4	6	0	0	FALSE		8	BAT
12	1	2	3	4	5	6	7	8	9	10	22	4	6	0	0	FALSE		9	BAT
13	1	2	3	4	5	6	7	8	9	11	14	4	6	1	0	FALSE		10	BAT
14	1	2	3	4	5	6	7	8	9	11	15	4	6	1	0	FALSE		11	BAT
15	1	2	3	4	5	6	7	8	9	11	19	4	6	0	0	FALSE		12	ALL
16	1	2	3	4	5	6	7	8	9	11	20	4	6	0	0	FALSE		13	ALL
17	1	2	3	4	5	6	7	8	9	11	21	4	6	0	0	FALSE		14	ALL
18	1	2	3	4	5	6	7	8	9	11	22	4	6	0	0	FALSE		15	ALL
19	1	2	3	4	5	6	7	8	9	12	19	4	5	1	1	FALSE		16	BOWL
20	1	2	3	4	5	6	7	8	9	12	20	4	5	1	1	FALSE		17	BOWL
21	1	2	3	4	5	6	7	8	9	12	21	4	5	1	1	FALSE		18	BOWL
22	1	2	3	4	5	6	7	8	9	12	22	4	5	1	1	FALSE		19	BOWL
23	1	2	3	4	5	6	7	8	9	13	19	4	5	1	1	FALSE		20	BOWL
24	1	2	3	4	5	6	7	8	9	13	20	4	5	1	1	FALSE		21	BOWL
25	1	2	3	4	5	6	7	8	9	13	21	4	5	1	1	FALSE		22	BOWL
26	1	2	3	4	5	6	7	8	9	13	22	4	5	1	1	FALSE
27	1	2	3	4	5	6	7	8	9	14	16	4	5	1	1	FALSE
1

Cell Formulas
Range	Formula
L4:O27	L4	=IFERROR(SUMPRODUCT(--(LOOKUP(A4:K4,$R$4:$R$25,$S$4:$S$25)=L$3)),0)
P4:P27	P4	=AND((L4:O4>=L$1:O$1)*(L4:O4<=L$2:O$2))

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