Sort

I need a macro that takes a two column table of player names and averages (bowling) and sorts them into a specified # of teams so the resulting average per team is equal or as close to equal as possible. We don't have static teams, we assign new teams each week by average.

Considerations:
- always between 2 to 5 teams depending on the total # of players each week
- we use "mystery" bowlers to even out teams (i.e. if there are 11 players, we have 1 mystery player as the 12th and have 4 teams of 3).
- mystery bowler can be called Mystery1 and Mystery2 depending on how many are needed (rarely 2)
- mystery bowler would be assigned an average that equals the assigned teams average
- if possible, ability to set # of teams (e.g. if we know we have 11 players, we set it to 3 teams of 4 OR 4 teams of 3 and then run the macro)

Example team below.

Player	Overall Average
Jack	185
Jill	158
Chris	149
Pete	172
Scott	159
John	154
Mihir	167
Sally	162
James	156
Hank	165
Perry	166

Thanks for any help, I have been struggling with this one for a long time!

You might try sorting the players by their average. Then start going down the list from top to bottom putting them in team first going from highest average then the next player would be from from the bottom lowest to highest. Then repeat until all players have been assigned.

Sort macro:

Please Login or Register  to view this content.

There is also a formula version in the workbook, Column C1, pasted and drag down.

Sorry for the late reply, and thanks for yours! From a formatting perspective, this is exactly what I need!

However, when looking at a final team I notice that since these are in order of average, the team average will ultimately get skewed downward the higher the team #. For example, I put in 12 players (I added one more player (Nate 130 avg) into the table in the original post) and sorted for 4 teams using the macro.

The end result is that each team has a lower overall average as you progress. My goal is to sort the teams so the team averages are either equal, or as close to equal as they can get.

Is there any way to do that in this macro? Thanks in advance for your help!

(sorry for the format of this table, for some reason it would not let me post an html table)

Team 1 Team 2 Team 3 Team 4
Jack Pete Mihir Perry
Hank Sally Scott Jill
James John Chris Nate
Team Average: 169 163 158 151 (note averages going down, need the macro to sort players so the team averages are as close to the same as possible)

Thanks for the Add Rep.

This is great! I ran a few simulations and the team averages were always very close. Nice!

The only issue I ran into so far is if I change the # of teams. For example, try the same table but with 4 teams (12 players). It ends up with two teams of 4 and two teams of 2.

If I tried 15 players, 4 teams it distributed players perfectly. However, 15 players, 5 teams did not work. I'm guessing due to the pairing.

I tried to look at the code, but it's above my head Is there a way to adjust this to compensate? Thanks in advance again!