Random Team Selection

[normanbox]

Hello Everyone,

I'm guessing my question has been asked a few times already, but I can't find the answer. Here's the situation, I have a group of 50 individuals listed in cells A2:A51. Over the last couple of weeks (2 times per week for 2 weeks), I have been putting all 50 in random teams of 5 in cells F2:J6. While I've never repeated the same five, I've been noticing that at times some of the individuals have worked together before and I don't want that. My goal is to have Excel randomly put all names in for me in cells F2:J6 AND not repeat any combinations for any two people so each team is completely new and nobody will have worked with each other before. I calculate that there should be 1,225 combinations of 2 people, so I should be able to have a whole set of totally new teams. I have kept a history of the teams in cells M2:Q22. For simplicity, please use numbers to represent names of people. I also understand that the longer history I have the more difficult it will be to have a completely new team put together. If needed, I can stop and start over once I run out of combinations. Class goes for 10 weeks, so ideally I will go all 10 weeks without a repeat of any two individuals.

Your help is much appreciated! Thank you.

 

[Eric W]

Your problem is a variant of a problem called "The Social Golfer" problem. It's been around for at least 150 years, and no one's found a general solution for it, although solutions for special cases have been found.

I worked on it quite a bit here:

https://www.mrexcel.com/forum/excel...aring-excel-arrays-golf-draw-over-5-days.html

although that was for foursomes.

This link:

combinatorics - Social Golfer Problem - Quintets - Mathematics Stack Exchange

claims that 10 teams of five can last for 8 days before repeating, but doesn't give a solution. It provides a link to a PDF that supposedly gives an algorithm, which I don't have time to examine fully. I think your best bet might be to loosen your constraints a bit (2 people can be on the same team 2 times?) and set up a random and/or recursive search algorithm. Depending on how many actual solutions exist for your new constraints, it's possible the random search algorithm might stumble across an acceptable version.

 

[normanbox]

Thank you very much. I didn't realize it would be that complicated. I'll check out your links, but from the sounds of it, doing it manually might be the best bet.

 

[AhoyNC]

Take a look at this web site and click on the pairing generator. I think this may be able to build the teams for you.

Free round robin tournament schedule / pairings generator ? GolfSoftware.com