How to Create Randomized Teams for a Golf Scramble Based on Player Ranking.

My excel knowledge is limited so bare with me. Essentially I am part of a weekly golf group that meets up once a week to participate in a scramble. The making of teams usually turns into some kind of debacle so i suggested we just use excel to randomly generate the teams that way nobody can be held accountable. Essentially I have a list of names player ranking 1, 2, 3, 4, 5. Each week we are unsure exactly who will show up (out of about 35 potential players) then make the teams based on who is there. I would love the option to just "check mark" players as they show up, hit a"Create" button and excel spits out groups of 4 or 5 (with as close to 1, 2, 3, 4, 5 on each team as possible). I could see this being done by fitting the players into columns based on their ranking (all the 1's in one column, 2's in the next, ect.), then pulling one from each column for each team or figure out how to tell excel to pull random names from the entire list and add up their rankings to make the total "ranking score" as equal as possible for a pre-determined number of teams. It is unlikely there will be equal number of 1's, 2's, ect. any given week. Which of these methods would you recommend I get to work on or any other advice would be appreciated.

Thanks

.
Here is one way to create the teams :

Please Login or Register  to view this content.

Col I1 to I20 and everything to the right is used for teams creation. Nothing should be written in that area as it will be erased when creating the teams.

I don't know about the check marks but this project works by copying and pasting names to the list.

This is exactly what I was looking for, manually inputing the players that show up that day is not a big deal so this should work. Thank you

You are welcome.

Is there a way to assign the "MaxRatingDiff" to the lowest possible number that would return results? (in turn making the team averages as close as possible to each other)

Always use "1". That's as close as they can get to one another in rankings.

Hi all- If you have some teams with 4 players, some with 5, how is that handled? Do you want a higher average ranking on smaller teams to make up the difference? Or do you have some scoring mechanism to even the results?

I am curious as to why "1" will yield the best results. For example, when I run a sample with "1" the best team averages a ranking of 2.4 and the worst team a 3.2. When i change the MaxDifferential to "0.5" the worst team average rating is 3.0 and the best is 2.6. The second method yields teams with closer average ratings and thus is a more equal team. What am I missing?

.
I ran several examples using 3, 4 and 5 Teams.

Each Team number was run with 1 - 6 MaxRatingDiff


Here is the result:

# TEAMS	MAXRATE DIFF
5	1		1
5	2		1.5
5	3		1.5
5	4		2.75
5	5		2.75
5	6		4.25
4	1		0.7
4	2		1.73333
4	3		6.3333
4	4		3.8
4	5		4.16667
4	6		2.6
3	1		2.85744
3	2		0.429
3	3		0.714286
3	4		3.428571
3	5		3
3	6		0.714286

First of all, if you run the same simulations, your results will differ because the program is randomly choosing the team members. They are different each time, hence the MaxRatingDiff will be slightly different as well.

However, you can see that using the lowest MaxRatingDiff number usually produces the closest matched teams.


Hope that makes sense ?

Logit, Can you take this one step further and figure into your equation the fact that some couples want to play together? Thanks for any additional help. This is so close to what I have been looking for. Just need that extra step. Thanks!

.
One of the moderators will eventually point this out to you .... You should not (what is referred to as 'high jacking') high jack someone else's thread. You can refer to it in your own thread, using it as an example or reference to what you are seeking.
Post your own thread with a descriptive basis.

Now .. to your question ... I would take the easy way out (sorry, not too enthusiastic in retirement). I'd average the two people's rating together.
Then I'd list them together in the Players Name column.

Amy = 1
Cindy = 2

Average is 1.5

List them in Players Column as Amy & Cindy with rating of 1.5

Then proceed as usual with the teams.

Do you have any suggestions for accomplishing the above with non VBA code?

.
One of the moderators will eventually point this out to you .... You should not (what is referred to as 'high jacking') high jack someone else's thread. You can refer to it in your own thread, using it as an example or reference to what you are seeking.
Post your own thread with a descriptive basis.

Sorry about that, I was looking for possible solution(s) to my original post - I ran across your earlier post and found it interesting, however, I am looking for a "non VBA" solution...there is no attempt on my part to "high jack" someone's thread.