Need Vba codes for the following

Rajeev Muraleedharan · Apr 2, 2020

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 · Apr 3, 2020

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

2

3

29,400

6

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

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

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

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

1

WK

5

1

2

3

4

5

6

7

8

9

10

12

2

WK

6

1

2

3

4

5

6

7

8

9

10

13

3

WK

7

1

2

3

4

5

6

7

8

9

10

14

4

WK

8

1

2

3

4

5

6

7

8

9

10

15

5

BAT

9

1

2

3

4

5

6

7

8

9

10

19

6

BAT

10

1

2

3

4

5

6

7

8

9

10

20

7

BAT

11

1

2

3

4

5

6

7

8

9

10

21

8

BAT

12

1

2

3

4

5

6

7

8

9

10

22

9

BAT

13

1

2

3

4

5

6

7

8

9

11

14

10

BAT

14

1

2

3

4

5

6

7

8

9

11

15

11

BAT

15

1

2

3

4

5

6

7

8

9

11

19

12

ALL

16

1

2

3

4

5

6

7

8

9

11

20

13

ALL

17

1

2

3

4

5

6

7

8

9

11

21

14

ALL

18

1

2

3

4

5

6

7

8

9

11

22

15

ALL

19

1

2

3

4

5

6

7

8

9

12

19

16

BOWL

20

1

2

3

4

5

6

7

8

9

12

20

17

BOWL

21

1

2

3

4

5

6

7

8

9

12

21

18

BOWL

22

1

2

3

4

5

6

7

8

9

12

22

19

BOWL

23

1

2

3

4

5

6

7

8

9

13

19

20

BOWL

24

1

2

3

4

5

6

7

8

9

13

20

21

BOWL

25

1

2

3

4

5

6

7

8

9

13

21

22

BOWL

26

1

2

3

4

5

6

7

8

9

13

22

27

1

2

3

4

5

6

7

8

9

14

16

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))

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 · Apr 3, 2020

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 · Apr 4, 2020

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

1

WK

AUS

8.5

5

1

2

3

4

5

6

7

8

9

10

12

2

WK

AUS

5.5

6

1

2

3

4

5

6

7

8

9

10

13

3

WK

NZ

4.5

7

1

2

3

4

5

6

7

8

9

10

14

4

WK

NZ

9.5

8

1

2

3

4

5

6

7

8

9

10

15

5

BAT

AUS

13.5

9

1

2

3

4

5

6

7

8

9

10

16

6

BAT

AUS

6.5

10

1

2

3

4

5

6

7

8

9

10

17

7

BAT

AUS

13.5

11

1

2

3

4

5

6

7

8

9

10

18

8

BAT

AUS

12.5

12

1

2

3

4

5

6

7

8

9

10

19

9

BAT

NZ

14.5

13

1

2

3

4

5

6

7

8

9

10

20

10

BAT

NZ

6.5

14

1

2

3

4

5

6

7

8

9

10

21

11

BAT

NZ

8.5

15

1

2

3

4

5

6

7

8

9

10

22

12

ALL

AUS

5.5

16

1

2

3

4

5

6

7

8

9

11

12

13

ALL

AUS

12.5

17

1

2

3

4

5

6

7

8

9

11

13

14

ALL

NZ

8.5

18

1

2

3

4

5

6

7

8

9

11

14

15

ALL

NZ

4.5

19

1

2

3

4

5

6

7

8

9

11

15

16

BOWL

AUS

6.5

20

1

2

3

4

5

6

7

8

9

11

16

17

BOWL

AUS

9.5

21

1

2

3

4

5

6

7

8

9

11

17

18

BOWL

AUS

14.5

22

1

2

3

4

5

6

7

8

9

11

18

19

BOWL

NZ

7.5

23

1

2

3

4

5

6

7

8

9

11

19

20

BOWL

NZ

5.5

24

1

2

3

4

5

6

7

8

9

11

20

21

BOWL

NZ

14.5

25

1

2

3

4

5

6

7

8

9

11

21

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 · Apr 4, 2020

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

StephenCrump · Apr 4, 2020

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 · Apr 4, 2020

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 · Apr 4, 2020

Is there any changes in vba codes??

StephenCrump · Apr 4, 2020

Rajeev Muraleedharan said:
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.

Rajeev Muraleedharan said:
Is there any changes in vba codes??

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

Dannydev · Oct 22, 2020

StephenCrump said:
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))

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

Need Vba codes for the following

Rajeev Muraleedharan

New Member

Excel Facts

StephenCrump

MrExcel MVP

Rajeev Muraleedharan

New Member

StephenCrump

MrExcel MVP

Rajeev Muraleedharan

New Member

StephenCrump

MrExcel MVP

Rajeev Muraleedharan

New Member

Rajeev Muraleedharan

New Member

StephenCrump

MrExcel MVP

Dannydev

New Member

Similar threads

Forum statistics

Share this page

Need Vba codes for the following

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock