randomly group golfer into teams of 4 with no repeats over 3 days

golfnut1

New Member
Joined
Jan 14, 2006
Messages
9
I've created a spreadsheet where I have a list of 24 golfers. These golfers have 12 "A" players and 12 "B" players. When I hit the sort button, they are randomly divided into 6 teams of 4 with 2 "A" players and 2 "B" players on each team. Now I want to do this for 3 days with no one golfer playing on the same team with another golfer that he has already played with. In other words, each golfer should have 9 different team mates over the 3 days. I'm not sure how to do this. I'd appreciate any help. Thanks.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,797
Office Version
  1. 365
Platform
  1. Windows
This is the well known and extensively analysed "Social Golfer Problem".

The general question: is it possible to have X groups of Y people playing Z games, without anyone playing more than once with any other player, is an unsolved problem.

In your case though the numbers are relatively small, and with a little bit of trial and error I found one possible solution as:

Event 1
A1, A2, B1, B2
A3, A4, B3, B4
A5, A6, B5, B6
A7, A8, B7, B8
A9, A10, B9, B10
A11, A12, B11, B12
Event 2
A2, A3, B6, B7
A4, A5, B8, B9
A6, A7, B10, B11
A8, A9, B12, B1
A10, A11, B2, B3
A12, A1, B4, B5
Event 3
A1, A4, B6, B11
A2, A5, B3, B10
A3, A7, B1, B5
A6, A10, B7, B12
A8, A11, B4, B9
A9, A12, B8, B2

<tbody>
</tbody>

Now just randomly allocate your players to the A1 to A12 and B1 to B12 slots.
 

golfnut1

New Member
Joined
Jan 14, 2006
Messages
9
Thank you so much, StephanCrump. I was making it more complicated than it was. Your help was very much appreciated.
 

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,797
Office Version
  1. 365
Platform
  1. Windows
You're welcome.

Happy golfing! (Or networking?)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,652
Messages
5,524,098
Members
409,559
Latest member
rjoslin414

This Week's Hot Topics

Top