Perfect brackets for online tournament [VBA] [Very advanced]

Brockenspook

New Member
Joined
Jun 18, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi, everybody

In a couple of weeks I will run an online tournament. In this tournament there will be between 11-60 players. Every player will play 5 matches (1 match every round). A match consist of 1 game between 5-10 participants. I want to find the perfect matches, which is rougly defined as a bracket wherein the same players play against each other as little as possible. A system to score the brackets based on this criterion is already developed. However, if I really want to know what bracket does have the best score, I have to try an extremely high amount of possibilities. It would be great if this process can be automated and I can for example run it for an hour for 35 players and then the bracket with the best score can be stored. I can then repeat this process 50 times from 11-60 players and have the perfect brackets for every player count.

I have worked this out further in my current Excel file. On every sheet there is a short explanation on what the sheet does. If you have any questions, please ask.

Sheet 1
Tournament Best Bracket Formula.xlsx
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO
2Every player gets assigned a random number. Every player needs to be able to play in every round, but can only play in one match per round. The amount of matches per round depend on how many people show up.
3You round up the number of digits to tens and divide it by 10 to get the number of matches. So if 35 people show up you will have 4 matches per round. Try to distribute the players as evenly as possible over the matches.
4So in case there are 35 players the players per match will be 9, 9, 9 and 8 per round.
5
611019281122334130241813024181173314
721120291021328126352912635293011278
83122130193061723171152317115245212
941322312841526342822163428221618341531
1051423322132435104332710433271228925
1161524331122339211593211593622319
1271625342031718322620143226201435163213
13817263529516278231258231252910267
149182731425191371913723420
15
16
17This is called a round (fill-in)
18This is called a match (fill-in)
Teams


Sheet 2 (not complete)
Tournament Best Bracket Formula.xlsx
BCDEFGHIJKLMNOPQRSTUV
2We try to minimize the amount of times people need to play together. In B5 you can find that player 1 and 2 play to gether.
3
4M1-1M2-1M3-1M4-1M5-1M6-1M7-1M8-1M9-1 M19-19M20-19M21-19M22-19M23-19M24-19M25-19M26-19M27-19 
5M1-2M2-2M3-2M4-2M5-2M6-2M7-2M8-2M9-2 M19-20M20-20M21-20M22-20M23-20M24-20M25-20M26-20M27-20 
6M1-3M2-3M3-3M4-3M5-3M6-3M7-3M8-3M9-3 M19-21M20-21M21-21M22-21M23-21M24-21M25-21M26-21M27-21 
7M1-4M2-4M3-4M4-4M5-4M6-4M7-4M8-4M9-4 M19-22M20-22M21-22M22-22M23-22M24-22M25-22M26-22M27-22 
8M1-5M2-5M3-5M4-5M5-5M6-5M7-5M8-5M9-5 M19-23M20-23M21-23M22-23M23-23M24-23M25-23M26-23M27-23 
9M1-6M2-6M3-6M4-6M5-6M6-6M7-6M8-6M9-6 M19-24M20-24M21-24M22-24M23-24M24-24M25-24M26-24M27-24 
10M1-7M2-7M3-7M4-7M5-7M6-7M7-7M8-7M9-7 M19-25M20-25M21-25M22-25M23-25M24-25M25-25M26-25M27-25 
11M1-8M2-8M3-8M4-8M5-8M6-8M7-8M8-8M9-8 M19-26M20-26M21-26M22-26M23-26M24-26M25-26M26-26M27-26 
12M1-9M2-9M3-9M4-9M5-9M6-9M7-9M8-9M9-9 M19-27M20-27M21-27M22-27M23-27M24-27M25-27M26-27M27-27 
13                    
14
15M10-10M11-10M12-10M13-10M14-10M15-10M16-10M17-10M18-10 M28-28M29-28M30-28M31-28M32-28M33-28M34-28M35-28  
16M10-11M11-11M12-11M13-11M14-11M15-11M16-11M17-11M18-11 M28-29M29-29M30-29M31-29M32-29M33-29M34-29M35-29  
17M10-12M11-12M12-12M13-12M14-12M15-12M16-12M17-12M18-12 M28-30M29-30M30-30M31-30M32-30M33-30M34-30M35-30  
18M10-13M11-13M12-13M13-13M14-13M15-13M16-13M17-13M18-13 M28-31M29-31M30-31M31-31M32-31M33-31M34-31M35-31  
19M10-14M11-14M12-14M13-14M14-14M15-14M16-14M17-14M18-14 M28-32M29-32M30-32M31-32M32-32M33-32M34-32M35-32  
20M10-15M11-15M12-15M13-15M14-15M15-15M16-15M17-15M18-15 M28-33M29-33M30-33M31-33M32-33M33-33M34-33M35-33  
21M10-16M11-16M12-16M13-16M14-16M15-16M16-16M17-16M18-16 M28-34M29-34M30-34M31-34M32-34M33-34M34-34M35-34  
22M10-17M11-17M12-17M13-17M14-17M15-17M16-17M17-17M18-17 M28-35M29-35M30-35M31-35M32-35M33-35M34-35M35-35  
23M10-18M11-18M12-18M13-18M14-18M15-18M16-18M17-18M18-18           
24                    
Overview match-ups
Cell Formulas
RangeFormula
B4:B13B4=IF(OR(ISBLANK(Teams!$B$6),ISBLANK(Teams!$B6)),"","M"&Teams!$B$6&"-"&Teams!$B6)
C4:C13C4=IF(OR(ISBLANK(Teams!$B$7),ISBLANK(Teams!$B6)),"","M"&Teams!$B$7&"-"&Teams!$B6)
D4:D13D4=IF(OR(ISBLANK(Teams!$B$8),ISBLANK(Teams!$B6)),"","M"&Teams!$B$8&"-"&Teams!$B6)
E4:E13E4=IF(OR(ISBLANK(Teams!$B$9),ISBLANK(Teams!$B6)),"","M"&Teams!$B$9&"-"&Teams!$B6)
F4:F13F4=IF(OR(ISBLANK(Teams!$B$10),ISBLANK(Teams!$B6)),"","M"&Teams!$B$10&"-"&Teams!$B6)
G4:G13G4=IF(OR(ISBLANK(Teams!$B$11),ISBLANK(Teams!$B6)),"","M"&Teams!$B$11&"-"&Teams!$B6)
H4:H13H4=IF(OR(ISBLANK(Teams!$B$12),ISBLANK(Teams!$B6)),"","M"&Teams!$B$12&"-"&Teams!$B6)
I4:I13I4=IF(OR(ISBLANK(Teams!$B$13),ISBLANK(Teams!$B6)),"","M"&Teams!$B$13&"-"&Teams!$B6)
J4:J13J4=IF(OR(ISBLANK(Teams!$B$14),ISBLANK(Teams!$B6)),"","M"&Teams!$B$14&"-"&Teams!$B6)
K4:K13K4=IF(OR(ISBLANK(Teams!$B$15),ISBLANK(Teams!$B6)),"","M"&Teams!$B$15&"-"&Teams!$B6)
M4:M13M4=IF(OR(ISBLANK(Teams!$D$6),ISBLANK(Teams!$D6)),"","M"&Teams!$D$6&"-"&Teams!$D6)
N4:N13N4=IF(OR(ISBLANK(Teams!$D$7),ISBLANK(Teams!$D6)),"","M"&Teams!$D$7&"-"&Teams!$D6)
O4:O13O4=IF(OR(ISBLANK(Teams!$D$8),ISBLANK(Teams!$D6)),"","M"&Teams!$D$8&"-"&Teams!$D6)
P4:P13P4=IF(OR(ISBLANK(Teams!$D$9),ISBLANK(Teams!$D6)),"","M"&Teams!$D$9&"-"&Teams!$D6)
Q4:Q13Q4=IF(OR(ISBLANK(Teams!$D$10),ISBLANK(Teams!$D6)),"","M"&Teams!$D$10&"-"&Teams!$D6)
R4:R13R4=IF(OR(ISBLANK(Teams!$D$11),ISBLANK(Teams!$D6)),"","M"&Teams!$D$11&"-"&Teams!$D6)
S4:S13S4=IF(OR(ISBLANK(Teams!$D$12),ISBLANK(Teams!$D6)),"","M"&Teams!$D$12&"-"&Teams!$D6)
T4:T13T4=IF(OR(ISBLANK(Teams!$D$13),ISBLANK(Teams!$D6)),"","M"&Teams!$D$13&"-"&Teams!$D6)
U4:U13U4=IF(OR(ISBLANK(Teams!$D$14),ISBLANK(Teams!$D6)),"","M"&Teams!$D$14&"-"&Teams!$D6)
V4:V13V4=IF(OR(ISBLANK(Teams!$D$15),ISBLANK(Teams!$D6)),"","M"&Teams!$D$15&"-"&Teams!$D6)
B15:B24B15=IF(OR(ISBLANK(Teams!$C$6),ISBLANK(Teams!$C6)),"","M"&Teams!$C$6&"-"&Teams!$C6)
C15:C24C15=IF(OR(ISBLANK(Teams!$C$7),ISBLANK(Teams!$C6)),"","M"&Teams!$C$7&"-"&Teams!$C6)
D15:D24D15=IF(OR(ISBLANK(Teams!$C$8),ISBLANK(Teams!$C6)),"","M"&Teams!$C$8&"-"&Teams!$C6)
E15:E24E15=IF(OR(ISBLANK(Teams!$C$9),ISBLANK(Teams!$C6)),"","M"&Teams!$C$9&"-"&Teams!$C6)
F15:F24F15=IF(OR(ISBLANK(Teams!$C$10),ISBLANK(Teams!$C6)),"","M"&Teams!$C$10&"-"&Teams!$C6)
G15:G24G15=IF(OR(ISBLANK(Teams!$C$11),ISBLANK(Teams!$C6)),"","M"&Teams!$C$11&"-"&Teams!$C6)
H15:H24H15=IF(OR(ISBLANK(Teams!$C$12),ISBLANK(Teams!$C6)),"","M"&Teams!$C$12&"-"&Teams!$C6)
I15:I24I15=IF(OR(ISBLANK(Teams!$C$13),ISBLANK(Teams!$C6)),"","M"&Teams!$C$13&"-"&Teams!$C6)
J15:J24J15=IF(OR(ISBLANK(Teams!$C$14),ISBLANK(Teams!$C6)),"","M"&Teams!$C$14&"-"&Teams!$C6)
K15:K24K15=IF(OR(ISBLANK(Teams!$C$15),ISBLANK(Teams!$C6)),"","M"&Teams!$C$15&"-"&Teams!$C6)
M15:M24M15=IF(OR(ISBLANK(Teams!$E$6),ISBLANK(Teams!$E6)),"","M"&Teams!$E$6&"-"&Teams!$E6)
N15:N24N15=IF(OR(ISBLANK(Teams!$E$7),ISBLANK(Teams!$E6)),"","M"&Teams!$E$7&"-"&Teams!$E6)
O15:O24O15=IF(OR(ISBLANK(Teams!$E$8),ISBLANK(Teams!$E6)),"","M"&Teams!$E$8&"-"&Teams!$E6)
P15:P24P15=IF(OR(ISBLANK(Teams!$E$9),ISBLANK(Teams!$E6)),"","M"&Teams!$E$9&"-"&Teams!$E6)
Q15:Q24Q15=IF(OR(ISBLANK(Teams!$E$10),ISBLANK(Teams!$E6)),"","M"&Teams!$E$10&"-"&Teams!$E6)
R15:R24R15=IF(OR(ISBLANK(Teams!$E$11),ISBLANK(Teams!$E6)),"","M"&Teams!$E$11&"-"&Teams!$E6)
S15:S24S15=IF(OR(ISBLANK(Teams!$E$12),ISBLANK(Teams!$E6)),"","M"&Teams!$E$12&"-"&Teams!$E6)
T15:T24T15=IF(OR(ISBLANK(Teams!$E$13),ISBLANK(Teams!$E6)),"","M"&Teams!$E$13&"-"&Teams!$E6)
U15:U24U15=IF(OR(ISBLANK(Teams!$E$14),ISBLANK(Teams!$E6)),"","M"&Teams!$E$14&"-"&Teams!$E6)
V15:V24V15=IF(OR(ISBLANK(Teams!$E$15),ISBLANK(Teams!$E6)),"","M"&Teams!$E$15&"-"&Teams!$E6)


Sheet 3 (not complete)
Tournament Best Bracket Formula.xlsx
BCDEFGHIJKLMNOPQRST
2351M1-22This sheet counts the amount of times people play together. In G2 you can find that player 1-2 play together 2 times.
32M1-32
43M1-41Amount of participating players (fill-in)
54M1-51
65M1-62
76M1-71
87M1-83
98M1-91
109M1-103
1110M1-111
1211M1-123
1312M1-130
1413M1-140
1514M1-150
1615M1-160
1716M1-170
1817M1-181
1918M1-193
2019M1-201
2120M1-212
2221M1-220
2322M1-233
2423M1-241
2524M1-250
2625M1-260
2726M1-270
2827M1-281
2928M1-292
3029M1-301
3130M1-310
3231M1-322
3332M1-330
3433M1-342
3534M1-351
3635  
Match-ups counter
Cell Formulas
RangeFormula
F2:F36F2=IF(ROW(A1)<$B$2,"M"&$D$2&"-"&D3,"")
G2:G36G2=IF(F2="","",COUNTIF('Overview match-ups'!$B$4:$FI$24,'Match-ups counter'!F2))
D2:D36D2=IF(ROW(A1)<=$B$2,ROW(A1),"")


Sheet 4 (not complete)
Tournament Best Bracket Formula.xlsx
BCDEFGHIJKLMNOPQRST
2In the amount of times people play together are sorted and scored. We want to let the same people play together as little as possible. Therefore this scoring system is set up. The final score has to be as low as possible.
3
40x together1x together2x together3x together4x together5x together0x together1x together2x together3x together4x together5x together
5M1-13M1-4M1-2M1-8#CALC!#CALC!18820912375001376
6M1-14M1-5M1-3M1-1001491625
7M1-15M1-7M1-6M1-12Final score
8M1-16M1-9M1-21M1-19
9M1-17M1-11M1-29M1-23
10M1-22M1-18M1-32M2-4
11M1-25M1-20M1-34M2-6
12M1-26M1-24M2-3M2-13
13M1-27M1-28M2-7M2-28
14M1-31M1-30M2-8M3-5
15M1-33M1-35M2-15M3-27
16M2-12M2-5M2-17M3-29
17M2-16M2-9M2-19M4-5
18M2-18M2-10M2-26M4-6
19M2-21M2-11M2-30M4-13
20M2-22M2-14M3-9M4-17
21M2-23M2-20M3-14M4-28
22M2-24M2-25M3-16M4-30
23M2-27M2-29M3-18M5-14
24M2-32M2-31M3-20M5-16
25M2-33M3-4M3-25M6-15
26M2-34M3-6M4-15M6-30
27M2-35M3-7M4-22M7-9
Scoring match-ups
Cell Formulas
RangeFormula
B5:B192B5=FILTER('Match-ups counter'!F2:F1771,'Match-ups counter'!G2:G1771=0)
C5:C213C5=FILTER('Match-ups counter'!F2:F1771,'Match-ups counter'!G2:G1771=1)
D5:D127D5=FILTER('Match-ups counter'!F2:F1771,'Match-ups counter'!G2:G1771=2)
E5:E79E5=FILTER('Match-ups counter'!F2:F1771,'Match-ups counter'!G2:G1771=3)
F5F5=FILTER('Match-ups counter'!F2:F1771,'Match-ups counter'!G2:G1771=4)
G5G5=FILTER('Match-ups counter'!F2:F1771,'Match-ups counter'!G2:G1771=5)
I5:N5I5=COUNTIF(B5:B1048576,"*")
I6I6=0^2
J6J6=1^2
K6K6=2^2
L6L6=3^2
M6M6=4^2
N6N6=5^2
P5P5=I5*I6+J5*J6+K5*K6+L5*L6+M5*M6+N5*N6
Dynamic array formulas.


With kind regards,
Brockspook
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,528
Welcome to MrExcel!

This is called the Social Golfer's problem, or sometimes Kirkman's Schoolgirl problem. There's a lot of literature on it. You can search this forum or the internet using those terms and find a lot. The short answer is that it's a very difficult problem. There's no defined algorithm to solve, except in some special cases. You could randomly assign teams, calculate the rating, and if it is better than the currently saved assignment, save it. And as you said, keep going for an hour, or until some target rating is found. I don't have time to do something like that for you right now, although someone else might give it a go.

In the meantime, check out this link:


This allows you to set up matches with 2-8 people per match, ensuring the minimum number of repeats as possible. This might get you part way to your goal.
 

Brockenspook

New Member
Joined
Jun 18, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
It is great to have some direction. I found this link Good-Enough Golfers which gives me the opportunity to solve for 12, 14, 16, 18, 20, 21, 24, 27, 30, 32, 36, 40, 45, 50, 54 and 60 players. The biggest problem that still remains is that I don't think there are any "calculators" that allow you to customize the group size and therefore it is impossible for me to come to a conclusion with regards to prime numbers and numbers like 35 where I need to be able to use 9, 9, 9 and 8 (because the optimal group size is 10) instead of 7, 7, 7, 7 and 7. This definitely put me on the right track, thanks a lot for the help!
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,441
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
That site does not yield best results. :(

I ran a test for 10 people, 2 groups, 5 matches and it yielded:

VBA Code:
Player 1 plays:
    2    3    4    5    6    7    8    9    10
    3x   3x   0x   1x   2x   4x   2x   3x   2x
 

Brockenspook

New Member
Joined
Jun 18, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

That's unfortunate, I didn't test it that much. I can however still try to repeat this for like 10 minutes and save the best option. But that would be really time consuming. I thought about this some more and I don't think I really need to have the best brackets from 11-60. I do need the brackets from 11-31. Then I can compute the 32 player bracket for example by using 2x the 16 player bracket. Doing so will also significantly reduce the waiting time.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,441
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
If you are a rocket scientist, this may help. It explains the formulas used for the social golfer problem
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,441
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
If you speak any other programming languages, you might look here for some coding examples.
 

Forum statistics

Threads
1,143,839
Messages
5,721,093
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top