random team generator for hockey with two sets of data

[jeremyp52]

Hello,

I am trying to build an excel spreadsheet for a hockey program I'm coaching that will do the following:

• Create 4 teams
• There will be a Team 1 White/Dark, and Team 2 White/Dark
• Use 2 lists to distribute players evenly
• There are a disproportionate amount of players on each list. There are 8 Majors, 12 Minors. 2 Majors maximum need to be on every team.
• Every week, the roster will be different (some don't show). I need to be able to hit 'randomize' and exclude certain people, and still have the lines 'relatively' similar

This is an essence two 3 vs 3 games, with a white vs. dark on each side of the ice (2 half-ice games). If there are 3 majors on each side (6 in attendance), one side will have a white with 2 majors, the other side will have a dark with 2 majors.

I'd also like a way to keep track of who's played with/against who, so if I can export that data set to ensure I don't get a duplicate team...or a primarily duplicate team.

[stnkynts]

can you submit an example workbook with before and after examples.

[jeremyp52]

roster example.xlsx

Please read remarks on each example given. I want to generate these separate Weekly rosters with a randomizer, hence the request for such a script.

[stnkynts]

Wow that took a lot more effort than I originally anticipated. Should be good to go now. Read my notes in code:

Please Login or Register  to view this content.

[jeremyp52]

Wow stnkynts that is absolutely fantastic. Simple and straightforward, exactly what I was looking for. I tried a million different variations of players and it works out perfectly. Thank you so much!!!