Picking random teams based on a number (golf handicap)

Hi Guys... I'm new to Excel but I'm amazed at its power.

So, I have a group of 80 people (golfers)... each has an individual handicap ranging from 5 to 36.

I need to group them into random teams of 4, so that each team gets a low number, a mid number, a mid number and a high number. e.g. Team A has 4 players with random handicaps of 8, 16, 18 & 28...

Can it be done? I will also, on other occasions, need to group them into teams of 3 so I'm guessing it will be a similar formula?

Thanks in advance

Gerry

Thanks Alan... you're correct... 20 low, 40 mid & 20 high... the team only plays for one day... each time that there's a new competition, a new team must be drawn/selected... it's very time consuming so an Excel formula will be brilliant. In the event of it being a team of 3, each team would have a low handicap, a middle handicap & a high handicap.
Thanks a lot for your help.

Thanks Alan... you're correct... 20 low, 40 mid & 20 high... the team only plays for one day... each time that there's a new competition, a new team must be drawn/selected... it's very time consuming so an Excel formula will be brilliant. In the event of it being a team of 3, each team would have a low handicap, a middle handicap & a high handicap.
Thanks a lot for your help.

Hi Alan... a quick question. If I use your workbook as a Master template, i) how will it work if we have more or less than 80 players and ii) how will it work for a competition for teams with 3 players?
Thanks again

Gerry

Thanks Popipipo... great work... on the second version the "iferror" appears when I click on the name cells... I'm an absolute beginner at Excel so I'm not sure if it's important??
I really appreciate your effort... thanks again.

Gerry

I get it... thanks... I'm learning all the time but I have a long way to go...

Hello again Popipipo... So, I've entered a new list of 28 of our members and their handicaps... it's an experiment just to see if I can get it to work ... If deleted all the names and handicaps that you had created... All the teams that you created were cleared...
What do I need to do to get the programme to run and create the teams? This will be a brilliant tool for the golf club as we run lots of team competitions during the summer and it takes us hours to draw the teams.
Thanks
Gerry

Originally Posted by popipipo

Delete all the names and handicaps in columns A and B

Fill then column A with new names and column B with handicaps

Ive entered 28 name + handicaps just to see if it will work for me... is there something that I need to do for the teams to be formed? At the moment, the cells are blank?

I think that I've attached it...

I've sorted the column by handicap, added a few new names but I still can't get the teams to form / generate? The cells are blank.

Originally Posted by Madzer

I've sorted the column by handicap, added a few new names but I still can't get the teams to form / generate? The cells are blank.

I should also have added, when I clicked on "enable editing" the teams on Excel_2 vanished?

When I open your attachment, Excel_2, the area with the teams, E6 to L11 is fully populated exactly as required. However, when I click the button to "Enable Editing" cells E6 to L11 immediately become blank.

So, after I entered the 28 names and associated handicaps, what's the next step that I should take to create the teams?

If you are using XL2010, as your profile states, then you won't have the XLOOKUP function available, which is used in the green cells in columns E to J. Because of the IFERROR function in those cells, they all appear blank.

Update your profile if you are using a later version of Excel.

Hope this helps.

Pete

You can change to INDEX/MATCH instead of XLOOKUP if you are still using a version that does not support it. Use these formulae in the cells stated on data3 sheet:

E6: =IFERROR(INDEX($A$2:$A$100,MATCH(M6,$C$2:$C$100,0)),"")

F6: =IFERROR(INDEX($B$2:$B$100,MATCH(M6,$C$2:$C$100,0)),"")

G6: =IFERROR(INDEX($A$2:$A$100,MATCH(N6,$C$2:$C$100,0)),"")

H6: =IFERROR(INDEX($B$2:$B$100,MATCH(N6,$C$2:$C$100,0)),"")

I6: =IFERROR(INDEX($A$2:$A$100,MATCH(O6,$C$2:$C$100,0)),"")

J6: =IFERROR(INDEX($B$2:$B$100,MATCH(O6,$C$2:$C$100,0)),"")

then copy them down. You will need to make similar changes to the data4 sheet.

Hope this helps.

Pete

Hi Guys... as I wrote previously, I'm completely out of my depth in Excel... but I gave quite a few hours trying your solutions by copying & pasting but to no avail. I didn't understand your formulae so, when it wasn't working, I didn't know what to change.

For now, I'm going to admit defeat.

I want to take this opportunity to thank you for your help and the time you put into it. I really appreciate it. If you're ever in Kilkee, Co. Clare, Ireland and you fancy a game of golf, drop me an email.

Gerry

Thanks for the reply... our club is using ScoreMaster and I'm not sure if it has the facility to do this... However, I will check it out... I believe that we're changing to BRS shortly so maybe they offer that option...

Brilliant... thanks... I cleared all the names & handicaps, repopulated the cells, sorted the Name/Handicap columns by handicap low to high and it generated balanced teams. I'm delighted. Thank you all.
Gerry.

Glad to hear that.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Originally Posted by Madzer

Thanks for the reply... our club is using ScoreMaster and I'm not sure if it has the facility to do this... However, I will check it out... I believe that we're changing to BRS shortly so maybe they offer that option...

Isn't ScoreMaster part of the HandicapMaster package. If so: This might be what you need:

13.7.2.4 How a Balanced Draw is calculated
The Start Sheets re-order option Reorder Players into 'Balanced' Groups is designed to reorder the
players so that each group has players with a low, a medium and a high handicap, etc. That is, each
group has broadly the same mix of player abilities.
How HandicapMaster creates a balanced draw
The calculation is based on the Handicap Index of those players already added to the Start Sheet grid.
1. The first column in the grid is filled with players with the lowest handicaps.
2. The last column is filled with players with the highest handicaps.
3. Any middle columns are filled with players with handicaps in the best range(s) possible.
4. Players in each column are then randomly shuffled (column by column), to create a more random
spread across the games.
Pairs Competitions
Where the Start Sheet is for a pairs competition (such as Foursomes, Four-ball, etc), HandicapMaster
will balance the players in each team pairing (rather than across all four players in each Tee Time).
Effectively, one player from the 50% of players with lower handicaps will be paired with one player from
the 50% with higher handicaps.
Notes
Once players have been re-ordered so there is one player from each range of handicaps in each
group, no attempt is made to balance up the total handicaps group by group.
If some individual cells have been marked as 'Unavailable', this can affect the accuracy of the results
of the reordering. It is not recommended that a balanced draw is attempted if the grid contains
isolated "unavailable" cells.

One popular method is using Excel's random number generation functions in combination with handicap ranges. This can be achieved by assigning a random number to each golfer and then categorizing them into groups based on their handicap range. You can then use these groups to form teams. Another approach, as mentioned by some contributors, involves using VBA (Visual Basic for Applications) to automate the team selection process. VBA provides more flexibility and control, allowing you to customize the algorithm according to your specific requirements.
found project