Unique combinations for surprise doubles tournament

RayL

New Member
Joined
Apr 16, 2020
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
Hi all,
I'm running a small dart tournament for the local club soon and I've prepared a little excel sheet (with hard codes) to handle the scheduling and results. While this works fine, I was wondering if it would be possible to have some formulas/vba code to make the whole thing a little easier (and more exciting :) ).Basically the conditions are as follows:
I have a list of participants (probably from 40 to 60 in total) that I list in a single column and allocate a player number.
From that list I randomly draw groups of 4 people to play a doubles match e.g Sam + Mary vs John + Sue, and each match is best of 3 legs.
Once everyone has played their matches in that round (some people may have a bye, depending on the number), I create a second round, and then a 3rd, 4th and even 5th if we have enough time.
Now here's the tricky part - no player should play WITH or AGAINST another play twice; e.g Sam should neither play with nor against Mary, John or Sue again (for as long as possible)
Currently, I simply split the players into 16 groups and have them play each other as listed in the table below, and this works fine up to 5 rounds, but it does have its limitations.

Is there an easier/more automatic way to create the unique doubles pairs to fulfil the criteria above, with formulas or code? I do have some VBA experience, but I have no idea how I would tackle this. :)

Looking forward to your responses.

1587029048245.png
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,336
Welcome to the MrExcel forum!

What you want is a variation of the Social Golfer problem. You can check out Google or Wikipedia for more information. What it boils down to is that it's a pretty tough problem. There's no easy way to get what you want. You can search this forum for "social golfer". I've written some large posts on the subject. It really can't be done with basic spreadsheet formulas/tools, you'd need to write a program to do so, which would take a lot of time and effort for someone. About the best I can offer at the moment is this website:


If you set it up for foursomes, with your starting number of people, you might be able to adapt the results to your situation.

Good luck!
 

RayL

New Member
Joined
Apr 16, 2020
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
Welcome to the MrExcel forum!

What you want is a variation of the Social Golfer problem. You can check out Google or Wikipedia for more information. What it boils down to is that it's a pretty tough problem. There's no easy way to get what you want. You can search this forum for "social golfer". I've written some large posts on the subject. It really can't be done with basic spreadsheet formulas/tools, you'd need to write a program to do so, which would take a lot of time and effort for someone. About the best I can offer at the moment is this website:


If you set it up for foursomes, with your starting number of people, you might be able to adapt the results to your situation.

Good luck!
Thanks Eric, I'll check it our :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,084
Messages
5,545,870
Members
410,711
Latest member
Josh324
Top