VBA to randomly select teams from list of players

[EinarG]

Excel 2013, Win 7 Pro

Starting with column A (Player Names) and column B (Player rating - value between 0-10), I need a macro to divide the players into either 2, 4 or 6 teams randomly. Column A would have less than 100 names. Teams are named TeamA, TeamB...TeamF. TeamA competes against TeamB, etc.

I have two parameters: NumTeams = Number of teams (2,4 or 6) and MaxRatingDiff = Maximum allowed Team Rating Difference between competing teams

Each team has a rating defined as the average of the ratings of the players on that team.

Competing teams either have the same number of players or one of them (B, D or F) can have an extra player when there is an odd number of players. Example: 23 players, 4 teams would result in player counts of TeamA=6, TeamB=6, TeamC=5, TeamD=6.

I need a VBA Macro to generate approximately even strength teams with the constraint that the difference in team rating between any two competing teams does not exceed MaxRatingDiff. Alternatively, a macro that can be re-run manually (using a command button for example) by the user until he/she is satisfied with the teams. The generated teams should be in columns (Name and Rating) sorted descending by the player rating.

Thanks for help on this or any portion of it!

 

[chuckf201]

I've made some mods to it also. I now include Standard Distribution numbers to determine teams with similar Std Dev. spreads and show with graphs.
I can share my spreadsheet if you would like to take a look.
Lately the billiards Club requested the teams to be sorted by the highest average (handicap) of player 1 of each time.
I'm at a lost on how to do a (bubble) sort on newly-created teams based upon Player-1 of each team.
Any ideas?

 

[Eric W]

@CarrolltonYM

If you see this, and this is the thread you referred to in your pm, send me your email address in a pm. I am unable to send you a pm until you have a certain number of posts.

 

[CarrolltonYM]

Thanks Eric! I sent you a PM. Hope it worked!

 

[chuckf201]

Hi EinarG,
Update...I've been using your code with great success and have expanded it to include normal distribution of the players.
Now, I have a question...I'm the 'print' section, I have a need to sort the teams by the number 1 player (Team Captain) so that
Team 1, player 1 has highest handicap and Team 6 (we have 6 teams total), player 1 has the lowest hanicap.

Thanks,
Chuckf

 

[Usercode]

Hi Chuckf201,

Is it possible to share a copy of your working excel file for me? without any sensitive information. Thanks.

 

[chuckf201]

Thanks for your reply. Having trouble pasting range but here are 3 of the 6 teams.

[TABLE="width: 578"]
<tbody>[TR]
[TD]Team A [/TD]
[TD] HCP[/TD]
[TD] Dist[/TD]
[TD]Team B[/TD]
[TD] HCP[/TD]
[TD] Dist[/TD]
[TD]Team C[/TD]
[TD] HCP[/TD]
[TD] Dist[/TD]
[/TR]
[TR]
[TD]KRAVITZ[/TD]
[TD="align: right"]0.605[/TD]
[TD="align: right"]2.892[/TD]
[TD] MARTINEZ[/TD]
[TD="align: right"]0.730[/TD]
[TD="align: right"]0.422[/TD]
[TD] LANE[/TD]
[TD="align: right"]0.688[/TD]
[TD="align: right"]0.188[/TD]
[/TR]
[TR]
[TD]DOBRESK[/TD]
[TD="align: right"]0.524[/TD]
[TD="align: right"]3.825[/TD]
[TD] BLANC[/TD]
[TD="align: right"]0.424[/TD]
[TD="align: right"]2.788[/TD]
[TD] SMITH[/TD]
[TD="align: right"]0.472[/TD]
[TD="align: right"]4.728[/TD]
[/TR]
[TR]
[TD]MATTSON[/TD]
[TD="align: right"]0.452[/TD]
[TD="align: right"]2.957[/TD]
[TD] EMERY[/TD]
[TD="align: right"]0.360[/TD]
[TD="align: right"]1.508[/TD]
[TD] WRIGHT[/TD]
[TD="align: right"]0.400[/TD]
[TD="align: right"]1.047[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

<tbody>
[TD="class: xl83, width: 49"][/TD]
[TD="class: xl83, width: 91"][/TD]
[TD="class: xl83, width: 58"][/TD]
[TD="class: xl83, width: 49"][/TD]
[TD="class: xl83, width: 74"][/TD]
[TD="class: xl83, width: 58"][/TD]
[TD="class: xl83, width: 117"][/TD]
[TD="class: xl83, width: 58"][/TD]
[TD="class: xl83, width: 49"][/TD]
[TD="class: xl83, width: 111"][/TD]
[TD="class: xl83, width: 58"][/TD]
[TD="class: xl83, width: 49"][/TD]
[TD="class: xl83, width: 126"][/TD]
[TD="class: xl83, width: 58"][/TD]
[TD="class: xl83, width: 49"][/TD]

[TD="class: xl84"][/TD]
[TD="class: xl86, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]
[TD="class: xl84"][/TD]
[TD="class: xl86, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]
[TD="class: xl84"][/TD]
[TD="class: xl86, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]
[TD="class: xl84"][/TD]
[TD="class: xl86, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]
[TD="class: xl84"][/TD]
[TD="class: xl86, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]
[TD="class: xl84"][/TD]
[TD="class: xl86, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]

[TD="class: xl84"][/TD]
[TD="class: xl85, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]
[TD="class: xl84"][/TD]
[TD="class: xl85, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]
[TD="class: xl84"][/TD]
[TD="class: xl85, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]
[TD="class: xl84"][/TD]
[TD="class: xl85, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]
[TD="class: xl84"][/TD]
[TD="class: xl85, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]
[TD="class: xl84"][/TD]
[TD="class: xl85, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]

[TD="class: xl84"][/TD]
[TD="class: xl85, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]
[TD="class: xl84"][/TD]
[TD="class: xl85, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]
[TD="class: xl84"][/TD]
[TD="class: xl85, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]
[TD="class: xl84"][/TD]
[TD="class: xl85, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]
[TD="class: xl84"][/TD]
[TD="class: xl85, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]
[TD="class: xl84"][/TD]
[TD="class: xl85, align: right"][/TD]
[TD="class: xl84, align: right"][/TD]

</tbody>

 

[Usercode]

Hi Chuckf201, I mean can you share a working copy of an excel file to select teams? Thanks

 

[chuckf201]

I can email a copy of the worksheet to you. I need an email address

 

[Peter_SSs]

I can email a copy of the worksheet to you.

Please refer to #4 of the Forum Rules
All information should remain in the public domain. Sample data should be posted directly in the thread or to a site that is available to all readers, with a link provided here in the thread.

 

[chuckf201]

Unfortunately the file size is 14M stored as xlsb.
The above post shows 3 teams with handicap and Dist. shown. Below is the total (6) teams.
I want player 1 of team 1 to have highest handicap of all top pllayers in each team. I have max of 6 teams total.
[TABLE="width: 1298"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Team A[/TD]
[TD]HCP[/TD]
[TD]Dist[/TD]
[TD]Team B[/TD]
[TD]HCP[/TD]
[TD]Dist[/TD]
[TD]Team C[/TD]
[TD]HCP[/TD]
[TD]Dist[/TD]
[TD]Team D[/TD]
[TD]HCP[/TD]
[TD]Dist[/TD]
[TD]Team E[/TD]
[TD]HCP[/TD]
[TD]Dist[/TD]
[TD]Team F[/TD]
[TD]HCP[/TD]
[TD]Dist[/TD]
[/TR]
[TR]
[TD]BRUSH[/TD]
[TD]0.636[/TD]
[TD]2.219[/TD]
[TD]LANE[/TD]
[TD]0.688[/TD]
[TD]1.359[/TD]
[TD]ANDERSON[/TD]
[TD]0.672[/TD]
[TD]1.842[/TD]
[TD]MARCAURELE[/TD]
[TD]0.624[/TD]
[TD]2.380[/TD]
[TD]MARTINEZ[/TD]
[TD]0.730[/TD]
[TD]1.190[/TD]
[TD]GONSALVES[/TD]
[TD]0.600[/TD]
[TD]1.340[/TD]
[/TR]
[TR]
[TD]DOBRESK[/TD]
[TD]0.524[/TD]
[TD]3.377[/TD]
[TD]SMITH[/TD]
[TD]0.472[/TD]
[TD]2.689[/TD]
[TD]MCKINZIE[/TD]
[TD]0.596[/TD]
[TD]3.126[/TD]
[TD]KRAVITZ[/TD]
[TD]0.605[/TD]
[TD]2.575[/TD]
[TD]MATTSON[/TD]
[TD]0.452[/TD]
[TD]1.763[/TD]
[TD]BRUESEWITZ[/TD]
[TD]0.570[/TD]
[TD]1.377[/TD]
[/TR]
[TR]
[TD]BLANC[/TD]
[TD]0.424[/TD]
[TD]2.292[/TD]
[TD]FINGERMAN[/TD]
[TD]0.392[/TD]
[TD]1.905[/TD]
[TD]EMERY[/TD]
[TD]0.360[/TD]
[TD]0.974[/TD]
[TD]GELINAS[/TD]
[TD]0.381[/TD]
[TD]1.526[/TD]
[TD]WRIGHT[/TD]
[TD]0.400[/TD]
[TD]1.568[/TD]
[TD]MIERZWIAK[/TD]
[TD]0.369[/TD]
[TD]1.235[/TD]
[/TR]
</tbody>[/TABLE]