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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,345
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,447
Messages
5,547,979
Members
410,820
Latest member
Prepost
Top