I've a sheet where am generating maximum combinations by using available players. In Batsmen section I've 8 players and generating 3 player combinations returned me 56 unique combinations. Where as in other sections,
WK
Among 3 Players with 2 player combinations: Got 3 unique combinations
ALL : Got 10 combinations
Bow: 15 Combinations
Bat: 56 Combinations (MAXIMUM)
In order to complete team of 11 players I must reuse the same combinations.
Here, I must Auto-fill 2 unique WK combinations to fill other 54 teams. For ALL 46 teams and Bow 41 teams.
For now am doing it manually. Is there any Macro code to do it automatically.
Here is the sheet am using now:
WK
Among 3 Players with 2 player combinations: Got 3 unique combinations
ALL : Got 10 combinations
Bow: 15 Combinations
Bat: 56 Combinations (MAXIMUM)
In order to complete team of 11 players I must reuse the same combinations.
Here, I must Auto-fill 2 unique WK combinations to fill other 54 teams. For ALL 46 teams and Bow 41 teams.
For now am doing it manually. Is there any Macro code to do it automatically.
Here is the sheet am using now:
GRAND LEAGUE TEAMS GENERATOR_TT4.xlsm | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | ||||
1 | WK | Batsmen | All Rounders | Bowlers | W.K | BAT | ALL | Bowl | VALIDATIONS | |||||||||||||||
2 | Baistrow | J Roy | Campher | D Willy | Player1 | Player2 | Player3 | Player4 | Player5 | Player6 | Player7 | Player8 | Player9 | Player10 | Player11 | Credits | IRE | ENG | Status | |||||
3 | Billings | Stirling | Delany | S Mahmood | T1 | Baistrow | Billings | J Roy | Stirling | Vince | Campher | Delany | D Willy | S Mahmood | McBrine | Rashid | 97.5 | 4 | 7 | VALID | ||||
4 | Tucker | Vince | T Curran | McBrine | T2 | Baistrow | Tucker | J Roy | Stirling | Balbarine | Campher | T Curran | D Willy | S Mahmood | McBrine | Young | 97.5 | 6 | 5 | VALID | ||||
5 | 0 | Balbarine | S Singh | Rashid | T3 | Billings | Tucker | J Roy | Stirling | Morgan | Campher | S Singh | D Willy | S Mahmood | McBrine | McKathy | 95.5 | 6 | 5 | VALID | ||||
6 | 0 | Morgan | M Ali | Young | T4 | J Roy | Stirling | K O Brain | Campher | M Ali | D Willy | S Mahmood | Rashid | Young | 80 | 4 | 5 | VALID | ||||||
7 | K O Brain | 0 | McKathy | T5 | J Roy | Stirling | Banton | Delany | T Curran | D Willy | S Mahmood | Rashid | McKathy | 79 | 3 | 6 | INVALID | |||||||
8 | Banton | 0 | 0 | T6 | J Roy | Stirling | Tector | Delany | S Singh | D Willy | S Mahmood | Young | McKathy | 78.5 | 6 | 3 | VALID | |||||||
9 | Tector | 0 | T7 | J Roy | Vince | Balbarine | Delany | M Ali | D Willy | McBrine | Rashid | Young | 79.5 | 4 | 5 | VALID | ||||||||
10 | 0 | 0 | T8 | J Roy | Vince | Morgan | T Curran | S Singh | D Willy | McBrine | Rashid | McKathy | 80 | 3 | 6 | INVALID | ||||||||
11 | T9 | J Roy | Vince | K O Brain | T Curran | M Ali | D Willy | McBrine | Young | McKathy | 80 | 4 | 5 | VALID | ||||||||||
12 | T10 | J Roy | Vince | Banton | S Singh | M Ali | D Willy | Rashid | Young | McKathy | 78.5 | 3 | 6 | INVALID | ||||||||||
13 | T11 | J Roy | Vince | Tector | S Mahmood | McBrine | Rashid | Young | 61 | 3 | 4 | INVALID | ||||||||||||
14 | T12 | J Roy | Balbarine | Morgan | S Mahmood | McBrine | Rashid | McKathy | 62.5 | 3 | 4 | INVALID | ||||||||||||
15 | T13 | J Roy | Balbarine | K O Brain | S Mahmood | McBrine | Young | McKathy | 62 | 5 | 2 | VALID | ||||||||||||
16 | T14 | J Roy | Balbarine | Banton | S Mahmood | Rashid | Young | McKathy | 61 | 3 | 4 | INVALID | ||||||||||||
17 | T15 | J Roy | Balbarine | Tector | McBrine | Rashid | Young | McKathy | 62 | 5 | 2 | VALID | ||||||||||||
18 | T16 | J Roy | Morgan | K O Brain | 29 | 1 | 2 | INVALID | ||||||||||||||||
19 | T17 | J Roy | Morgan | Banton | 27.5 | 0 | 3 | INVALID | ||||||||||||||||
20 | T18 | J Roy | Morgan | Tector | 28 | 1 | 2 | INVALID | ||||||||||||||||
21 | T19 | J Roy | K O Brain | Banton | 27.5 | 1 | 2 | INVALID | ||||||||||||||||
22 | T20 | J Roy | K O Brain | Tector | 28 | 2 | 1 | INVALID | ||||||||||||||||
23 | T21 | J Roy | Banton | Tector | 26.5 | 1 | 2 | INVALID | ||||||||||||||||
24 | T22 | Stirling | Vince | Balbarine | 27 | 2 | 1 | INVALID | ||||||||||||||||
25 | T23 | Stirling | Vince | Morgan | 27.5 | 1 | 2 | INVALID | ||||||||||||||||
26 | T24 | Stirling | Vince | K O Brain | 27.5 | 2 | 1 | INVALID | ||||||||||||||||
27 | T25 | Stirling | Vince | Banton | 26 | 1 | 2 | INVALID | ||||||||||||||||
28 | T26 | Stirling | Vince | Tector | 26.5 | 2 | 1 | INVALID | ||||||||||||||||
29 | T27 | Stirling | Balbarine | Morgan | 28 | 2 | 1 | INVALID | ||||||||||||||||
30 | T28 | Stirling | Balbarine | K O Brain | 28 | 3 | 0 | INVALID | ||||||||||||||||
31 | T29 | Stirling | Balbarine | Banton | 26.5 | 2 | 1 | INVALID | ||||||||||||||||
32 | T30 | Stirling | Balbarine | Tector | 27 | 3 | 0 | INVALID | ||||||||||||||||
33 | T31 | Stirling | Morgan | K O Brain | 28.5 | 2 | 1 | INVALID | ||||||||||||||||
34 | T32 | Stirling | Morgan | Banton | 27 | 1 | 2 | INVALID | ||||||||||||||||
35 | T33 | Stirling | Morgan | Tector | 27.5 | 2 | 1 | INVALID | ||||||||||||||||
36 | T34 | Stirling | K O Brain | Banton | 27 | 2 | 1 | INVALID | ||||||||||||||||
37 | T35 | Stirling | K O Brain | Tector | 27.5 | 3 | 0 | INVALID | ||||||||||||||||
38 | Stirling | Banton | Tector | |||||||||||||||||||||
39 | Vince | Balbarine | Morgan | |||||||||||||||||||||
40 | Vince | Balbarine | K O Brain | |||||||||||||||||||||
41 | Vince | Balbarine | Banton | |||||||||||||||||||||
42 | Vince | Balbarine | Tector | |||||||||||||||||||||
43 | Vince | Morgan | K O Brain | |||||||||||||||||||||
44 | Vince | Morgan | Banton | |||||||||||||||||||||
45 | Vince | Morgan | Tector | |||||||||||||||||||||
46 | Vince | K O Brain | Banton | |||||||||||||||||||||
47 | Vince | K O Brain | Tector | |||||||||||||||||||||
48 | Vince | Banton | Tector | |||||||||||||||||||||
49 | Balbarine | Morgan | K O Brain | |||||||||||||||||||||
50 | Balbarine | Morgan | Banton | |||||||||||||||||||||
51 | Balbarine | Morgan | Tector | |||||||||||||||||||||
52 | Balbarine | K O Brain | Banton | |||||||||||||||||||||
53 | Balbarine | K O Brain | Tector | |||||||||||||||||||||
54 | Balbarine | Banton | Tector | |||||||||||||||||||||
55 | Morgan | K O Brain | Banton | |||||||||||||||||||||
56 | Morgan | K O Brain | Tector | |||||||||||||||||||||
57 | Morgan | Banton | Tector | |||||||||||||||||||||
58 | K O Brain | Banton | Tector | |||||||||||||||||||||
2324_All |
Cell Formulas | ||
---|---|---|
Range | Formula | |
T2 | T2 | ='Master Sheet'!$C3 |
U2 | U2 | ='Master Sheet'!$C14 |
S3:S37 | S3 | =SUMPRODUCT(SUMIF('Master Sheet'!$B$3:$B$24,$H3:$R3,'Master Sheet'!$E$3:$E$24)) |
T3:U37 | T3 | =SUMPRODUCT(COUNTIFS('Master Sheet'!$B$3:$B$24,$H3:$R3,'Master Sheet'!$C$3:$C$24,T$2)) |
V3:V37 | V3 | =IF(OR(S3>100,T3<4,T3>7),"INVALID","VALID") |
A2:A6 | A2 | ='Master Sheet'!H3 |
C2:C8 | C2 | ='Master Sheet'!R3 |
B2:B10 | B2 | ='Master Sheet'!M3 |
D2:D10 | D2 | ='Master Sheet'!W3 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
S3:S37 | Cell Value | >100 | text | NO |
T3:U37 | Cell Value | >7 | text | NO |
V3:V37 | Cell Value | contains "INVALID" | text | NO |
V3:V37 | Other Type | Color scale | NO |