Team Selection Problem Solving with Data...Please help

[sur]

Hello Team,

Here is the Problem Statement, please help to resolve this:

[TABLE="width: 508"]
<colgroup><col><col span="11"></colgroup><tbody>[TR]
[TD="colspan: 4"]Problem Statement:
We need to select the best team for EACH STATE
Given a set of player information, some selection criteria and the required number of players at each position as input,automatically mark whether a player is selected or not for his state team
The worksheet should automatically handle the following changes:
- Changes in team composition (Number of Batsman, Number of Bowlers and Number of All Rounders)
- Addition of more states in the dataset
- Changes in the data set (Batting Averages / Wickets and Wicketkeeper flag)


[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Selection Criteria:

- There has to be one wicketkeeper-batsman in the team. In case there are multiple candidate wicketkeepers in the state, the one with highest batting average is selected.


- Select the remaining number of required batsmen in a decreasing order of their batting averages.


- Select the required number of bowlers in the decreasing order of wickets/match.


- Select the allrounders based on the formula (Batting average + 5*wickets/match). The higher this value, the better the allrounder and needs to be chosen



[/TD]
[TD="colspan: 7"]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
IMP:
[TABLE="width: 629"]
<colgroup><col span="12"><col></colgroup><tbody>[TR]
[TD="colspan: 12"]1) To get the selected players, you should write VBA macros which populate the value of isSelected[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 12"]- If needed, you can add columns to the right of isSelected which would help you in the processing[/TD]
[/TR]
[TR]
[TD="colspan: 13"]2) The submitted solution should be generic so that it should work on changing the Player Info and the required team composition[/TD]
[/TR]
</tbody>[/TABLE]


Per Team Composition:

Batsman 4
Bowler 3
All Rounder 4
Player Statistics:


[TABLE="width: 707"]
<colgroup><col span="7"></colgroup><tbody>[TR]
[TD]Player Name[/TD]
[TD]State[/TD]
[TD]Position[/TD]
[TD]Batting Average[/TD]
[TD]Wickets / Match[/TD]
[TD]IsWicketKeeper[/TD]
[TD]IsSelected[/TD]
[/TR]
[TR]
[TD]P1[/TD]
[TD]Maharashtra[/TD]
[TD]Batsman[/TD]
[TD]34[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD]Maharashtra[/TD]
[TD]Batsman[/TD]
[TD]32[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P3[/TD]
[TD]Maharashtra[/TD]
[TD]Batsman[/TD]
[TD]30[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P4[/TD]
[TD]Maharashtra[/TD]
[TD]Batsman[/TD]
[TD]24[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P5[/TD]
[TD]Maharashtra[/TD]
[TD]Batsman[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P6[/TD]
[TD]Maharashtra[/TD]
[TD]Batsman[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P7[/TD]
[TD]Maharashtra[/TD]
[TD]Bowler[/TD]
[TD]22[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P8[/TD]
[TD]Maharashtra[/TD]
[TD]Bowler[/TD]
[TD]21[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P9[/TD]
[TD]Maharashtra[/TD]
[TD]Bowler[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P10[/TD]
[TD]Maharashtra[/TD]
[TD]Bowler[/TD]
[TD]10[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P11[/TD]
[TD]Maharashtra[/TD]
[TD]Bowler[/TD]
[TD]15[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P12[/TD]
[TD]Maharashtra[/TD]
[TD]Bowler[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P13[/TD]
[TD]Maharashtra[/TD]
[TD]Bowler[/TD]
[TD]14[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P14[/TD]
[TD]Maharashtra[/TD]
[TD]Bowler[/TD]
[TD]19[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P15[/TD]
[TD]Maharashtra[/TD]
[TD]All Rounder[/TD]
[TD]32[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P16[/TD]
[TD]Maharashtra[/TD]
[TD]All Rounder[/TD]
[TD]13[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P17[/TD]
[TD]Maharashtra[/TD]
[TD]All Rounder[/TD]
[TD]27[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P18[/TD]
[TD]Maharashtra[/TD]
[TD]All Rounder[/TD]
[TD]14[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P19[/TD]
[TD]Maharashtra[/TD]
[TD]All Rounder[/TD]
[TD]41[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P20[/TD]
[TD]Maharashtra[/TD]
[TD]All Rounder[/TD]
[TD]15[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P21[/TD]
[TD]Karnataka[/TD]
[TD]Batsman[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P22[/TD]
[TD]Karnataka[/TD]
[TD]Batsman[/TD]
[TD]21[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P23[/TD]
[TD]Karnataka[/TD]
[TD]Batsman[/TD]
[TD]20[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P24[/TD]
[TD]Karnataka[/TD]
[TD]Batsman[/TD]
[TD]21[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P25[/TD]
[TD]Karnataka[/TD]
[TD]Batsman[/TD]
[TD]38[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P26[/TD]
[TD]Karnataka[/TD]
[TD]Batsman[/TD]
[TD]45[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P27[/TD]
[TD]Karnataka[/TD]
[TD]Bowler[/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P28[/TD]
[TD]Karnataka[/TD]
[TD]Bowler[/TD]
[TD]21[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P29[/TD]
[TD]Karnataka[/TD]
[TD]Bowler[/TD]
[TD]23[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P30[/TD]
[TD]Karnataka[/TD]
[TD]Bowler[/TD]
[TD]21[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P31[/TD]
[TD]Karnataka[/TD]
[TD]Bowler[/TD]
[TD]16[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P32[/TD]
[TD]Karnataka[/TD]
[TD]Bowler[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P33[/TD]
[TD]Karnataka[/TD]
[TD]Bowler[/TD]
[TD]23[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P34[/TD]
[TD]Karnataka[/TD]
[TD]Bowler[/TD]
[TD]23[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P35[/TD]
[TD]Karnataka[/TD]
[TD]All Rounder[/TD]
[TD]18[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P36[/TD]
[TD]Karnataka[/TD]
[TD]All Rounder[/TD]
[TD]30[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P37[/TD]
[TD]Karnataka[/TD]
[TD]All Rounder[/TD]
[TD]14[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P38[/TD]
[TD]Karnataka[/TD]
[TD]All Rounder[/TD]
[TD]41[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P39[/TD]
[TD]Karnataka[/TD]
[TD]All Rounder[/TD]
[TD]22[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P40[/TD]
[TD]Karnataka[/TD]
[TD]All Rounder[/TD]
[TD]34[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P41[/TD]
[TD]Karnataka[/TD]
[TD]Bowler[/TD]
[TD]25[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]