Auto Fill data based adjacent data

S

sekhar03

New Member
Joined
Jul 26, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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:

GRAND LEAGUE TEAMS GENERATOR_TT4.xlsm
ABCDEGHIJKLMNOPQRSTUV
1WKBatsmenAll RoundersBowlersW.KBATALLBowlVALIDATIONS
2BaistrowJ RoyCampherD WillyPlayer1Player2Player3Player4Player5Player6Player7Player8Player9Player10Player11CreditsIREENGStatus
3BillingsStirlingDelanyS MahmoodT1BaistrowBillingsJ RoyStirlingVinceCampherDelanyD WillyS MahmoodMcBrineRashid97.547VALID
4TuckerVinceT CurranMcBrineT2BaistrowTuckerJ RoyStirlingBalbarineCampherT CurranD WillyS MahmoodMcBrineYoung97.565VALID
50BalbarineS SinghRashidT3BillingsTuckerJ RoyStirlingMorganCampherS SinghD WillyS MahmoodMcBrineMcKathy95.565VALID
60MorganM AliYoungT4J RoyStirlingK O BrainCampherM AliD WillyS MahmoodRashidYoung8045VALID
7K O Brain0McKathyT5J RoyStirlingBantonDelanyT CurranD WillyS MahmoodRashidMcKathy7936INVALID
8Banton00T6J RoyStirlingTectorDelanyS SinghD WillyS MahmoodYoungMcKathy78.563VALID
9Tector0T7J RoyVinceBalbarineDelanyM AliD WillyMcBrineRashidYoung79.545VALID
1000T8J RoyVinceMorganT CurranS SinghD WillyMcBrineRashidMcKathy8036INVALID
11T9J RoyVinceK O BrainT CurranM AliD WillyMcBrineYoungMcKathy8045VALID
12T10J RoyVinceBantonS SinghM AliD WillyRashidYoungMcKathy78.536INVALID
13T11J RoyVinceTectorS MahmoodMcBrineRashidYoung6134INVALID
14T12J RoyBalbarineMorganS MahmoodMcBrineRashidMcKathy62.534INVALID
15T13J RoyBalbarineK O BrainS MahmoodMcBrineYoungMcKathy6252VALID
16T14J RoyBalbarineBantonS MahmoodRashidYoungMcKathy6134INVALID
17T15J RoyBalbarineTectorMcBrineRashidYoungMcKathy6252VALID
18T16J RoyMorganK O Brain2912INVALID
19T17J RoyMorganBanton27.503INVALID
20T18J RoyMorganTector2812INVALID
21T19J RoyK O BrainBanton27.512INVALID
22T20J RoyK O BrainTector2821INVALID
23T21J RoyBantonTector26.512INVALID
24T22StirlingVinceBalbarine2721INVALID
25T23StirlingVinceMorgan27.512INVALID
26T24StirlingVinceK O Brain27.521INVALID
27T25StirlingVinceBanton2612INVALID
28T26StirlingVinceTector26.521INVALID
29T27StirlingBalbarineMorgan2821INVALID
30T28StirlingBalbarineK O Brain2830INVALID
31T29StirlingBalbarineBanton26.521INVALID
32T30StirlingBalbarineTector2730INVALID
33T31StirlingMorganK O Brain28.521INVALID
34T32StirlingMorganBanton2712INVALID
35T33StirlingMorganTector27.521INVALID
36T34StirlingK O BrainBanton2721INVALID
37T35StirlingK O BrainTector27.530INVALID
38StirlingBantonTector
39VinceBalbarineMorgan
40VinceBalbarineK O Brain
41VinceBalbarineBanton
42VinceBalbarineTector
43VinceMorganK O Brain
44VinceMorganBanton
45VinceMorganTector
46VinceK O BrainBanton
47VinceK O BrainTector
48VinceBantonTector
49BalbarineMorganK O Brain
50BalbarineMorganBanton
51BalbarineMorganTector
52BalbarineK O BrainBanton
53BalbarineK O BrainTector
54BalbarineBantonTector
55MorganK O BrainBanton
56MorganK O BrainTector
57MorganBantonTector
58K O BrainBantonTector
2324_All
Cell Formulas
RangeFormula
T2T2='Master Sheet'!$C3
U2U2='Master Sheet'!$C14
S3:S37S3=SUMPRODUCT(SUMIF('Master Sheet'!$B$3:$B$24,$H3:$R3,'Master Sheet'!$E$3:$E$24))
T3:U37T3=SUMPRODUCT(COUNTIFS('Master Sheet'!$B$3:$B$24,$H3:$R3,'Master Sheet'!$C$3:$C$24,T$2))
V3:V37V3=IF(OR(S3>100,T3<4,T3>7),"INVALID","VALID")
A2:A6A2='Master Sheet'!H3
C2:C8C2='Master Sheet'!R3
B2:B10B2='Master Sheet'!M3
D2:D10D2='Master Sheet'!W3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
S3:S37Cell Value>100textNO
T3:U37Cell Value>7textNO
V3:V37Cell Valuecontains "INVALID"textNO
V3:V37Other TypeColor scaleNO
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA code for userform , to add data to specific cells based on combolistbox selection
Replies
6
Views
621
sloany101
S
MrDB4Excel
  • Question
If Find Blank Cell
Replies
0
Views
394
MrDB4Excel
MrDB4Excel
C
  • Solved
Need to Return Unique Players for Same Multiple Number Result (XLOOKUP?)
2
Replies
14
Views
931
Fluff
Fluff
PS_Richard
  • Question
Combo Graph with multiple sets of data in it
Replies
1
Views
439
PS_Richard
PS_Richard
S
  • Question
Generate all possible combinations
Replies
1
Views
2K
Phuoc
P

Forum statistics

Threads
1,214,881
Messages
6,122,074
Members
449,064
Latest member
MattDRT

Share this page

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?

Disable AdBlock

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
Back
Top