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

[Brockenspook]

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
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
2	Every 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.
3	You 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.
4	So in case there are 35 players the players per match will be 9, 9, 9 and 8 per round.
5
6	1	10	19	28				1	12	23	34				1	30	24	18				1	30	24	18				1	17	33	14
7	2	11	20	29				10	21	32	8				12	6	35	29				12	6	35	29				30	11	27	8
8	3	12	21	30				19	30	6	17				23	17	11	5				23	17	11	5				24	5	21	2
9	4	13	22	31				28	4	15	26				34	28	22	16				34	28	22	16				18	34	15	31
10	5	14	23	32				2	13	24	35				10	4	33	27				10	4	33	27				12	28	9	25
11	6	15	24	33				11	22	33	9				21	15	9	3				21	15	9	3				6	22	3	19
12	7	16	25	34				20	31	7	18				32	26	20	14				32	26	20	14				35	16	32	13
13	8	17	26	35				29	5	16	27				8	2	31	25				8	2	31	25				29	10	26	7
14	9	18	27					3	14	25					19	13	7					19	13	7					23	4	20
15
16
17		This is called a round (fill-in)
18		This is called a match (fill-in)
Teams

Sheet 2 (not complete)

Tournament Best Bracket Formula.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2	We 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
4	M1-1	M2-1	M3-1	M4-1	M5-1	M6-1	M7-1	M8-1	M9-1			M19-19	M20-19	M21-19	M22-19	M23-19	M24-19	M25-19	M26-19	M27-19
5	M1-2	M2-2	M3-2	M4-2	M5-2	M6-2	M7-2	M8-2	M9-2			M19-20	M20-20	M21-20	M22-20	M23-20	M24-20	M25-20	M26-20	M27-20
6	M1-3	M2-3	M3-3	M4-3	M5-3	M6-3	M7-3	M8-3	M9-3			M19-21	M20-21	M21-21	M22-21	M23-21	M24-21	M25-21	M26-21	M27-21
7	M1-4	M2-4	M3-4	M4-4	M5-4	M6-4	M7-4	M8-4	M9-4			M19-22	M20-22	M21-22	M22-22	M23-22	M24-22	M25-22	M26-22	M27-22
8	M1-5	M2-5	M3-5	M4-5	M5-5	M6-5	M7-5	M8-5	M9-5			M19-23	M20-23	M21-23	M22-23	M23-23	M24-23	M25-23	M26-23	M27-23
9	M1-6	M2-6	M3-6	M4-6	M5-6	M6-6	M7-6	M8-6	M9-6			M19-24	M20-24	M21-24	M22-24	M23-24	M24-24	M25-24	M26-24	M27-24
10	M1-7	M2-7	M3-7	M4-7	M5-7	M6-7	M7-7	M8-7	M9-7			M19-25	M20-25	M21-25	M22-25	M23-25	M24-25	M25-25	M26-25	M27-25
11	M1-8	M2-8	M3-8	M4-8	M5-8	M6-8	M7-8	M8-8	M9-8			M19-26	M20-26	M21-26	M22-26	M23-26	M24-26	M25-26	M26-26	M27-26
12	M1-9	M2-9	M3-9	M4-9	M5-9	M6-9	M7-9	M8-9	M9-9			M19-27	M20-27	M21-27	M22-27	M23-27	M24-27	M25-27	M26-27	M27-27
13
14
15	M10-10	M11-10	M12-10	M13-10	M14-10	M15-10	M16-10	M17-10	M18-10			M28-28	M29-28	M30-28	M31-28	M32-28	M33-28	M34-28	M35-28
16	M10-11	M11-11	M12-11	M13-11	M14-11	M15-11	M16-11	M17-11	M18-11			M28-29	M29-29	M30-29	M31-29	M32-29	M33-29	M34-29	M35-29
17	M10-12	M11-12	M12-12	M13-12	M14-12	M15-12	M16-12	M17-12	M18-12			M28-30	M29-30	M30-30	M31-30	M32-30	M33-30	M34-30	M35-30
18	M10-13	M11-13	M12-13	M13-13	M14-13	M15-13	M16-13	M17-13	M18-13			M28-31	M29-31	M30-31	M31-31	M32-31	M33-31	M34-31	M35-31
19	M10-14	M11-14	M12-14	M13-14	M14-14	M15-14	M16-14	M17-14	M18-14			M28-32	M29-32	M30-32	M31-32	M32-32	M33-32	M34-32	M35-32
20	M10-15	M11-15	M12-15	M13-15	M14-15	M15-15	M16-15	M17-15	M18-15			M28-33	M29-33	M30-33	M31-33	M32-33	M33-33	M34-33	M35-33
21	M10-16	M11-16	M12-16	M13-16	M14-16	M15-16	M16-16	M17-16	M18-16			M28-34	M29-34	M30-34	M31-34	M32-34	M33-34	M34-34	M35-34
22	M10-17	M11-17	M12-17	M13-17	M14-17	M15-17	M16-17	M17-17	M18-17			M28-35	M29-35	M30-35	M31-35	M32-35	M33-35	M34-35	M35-35
23	M10-18	M11-18	M12-18	M13-18	M14-18	M15-18	M16-18	M17-18	M18-18
24
Overview match-ups

Cell Formulas
Range	Formula
B4:B13	B4	=IF(OR(ISBLANK(Teams!$B$6),ISBLANK(Teams!$B6)),"","M"&Teams!$B$6&"-"&Teams!$B6)
C4:C13	C4	=IF(OR(ISBLANK(Teams!$B$7),ISBLANK(Teams!$B6)),"","M"&Teams!$B$7&"-"&Teams!$B6)
D4:D13	D4	=IF(OR(ISBLANK(Teams!$B$8),ISBLANK(Teams!$B6)),"","M"&Teams!$B$8&"-"&Teams!$B6)
E4:E13	E4	=IF(OR(ISBLANK(Teams!$B$9),ISBLANK(Teams!$B6)),"","M"&Teams!$B$9&"-"&Teams!$B6)
F4:F13	F4	=IF(OR(ISBLANK(Teams!$B$10),ISBLANK(Teams!$B6)),"","M"&Teams!$B$10&"-"&Teams!$B6)
G4:G13	G4	=IF(OR(ISBLANK(Teams!$B$11),ISBLANK(Teams!$B6)),"","M"&Teams!$B$11&"-"&Teams!$B6)
H4:H13	H4	=IF(OR(ISBLANK(Teams!$B$12),ISBLANK(Teams!$B6)),"","M"&Teams!$B$12&"-"&Teams!$B6)
I4:I13	I4	=IF(OR(ISBLANK(Teams!$B$13),ISBLANK(Teams!$B6)),"","M"&Teams!$B$13&"-"&Teams!$B6)
J4:J13	J4	=IF(OR(ISBLANK(Teams!$B$14),ISBLANK(Teams!$B6)),"","M"&Teams!$B$14&"-"&Teams!$B6)
K4:K13	K4	=IF(OR(ISBLANK(Teams!$B$15),ISBLANK(Teams!$B6)),"","M"&Teams!$B$15&"-"&Teams!$B6)
M4:M13	M4	=IF(OR(ISBLANK(Teams!$D$6),ISBLANK(Teams!$D6)),"","M"&Teams!$D$6&"-"&Teams!$D6)
N4:N13	N4	=IF(OR(ISBLANK(Teams!$D$7),ISBLANK(Teams!$D6)),"","M"&Teams!$D$7&"-"&Teams!$D6)
O4:O13	O4	=IF(OR(ISBLANK(Teams!$D$8),ISBLANK(Teams!$D6)),"","M"&Teams!$D$8&"-"&Teams!$D6)
P4:P13	P4	=IF(OR(ISBLANK(Teams!$D$9),ISBLANK(Teams!$D6)),"","M"&Teams!$D$9&"-"&Teams!$D6)
Q4:Q13	Q4	=IF(OR(ISBLANK(Teams!$D$10),ISBLANK(Teams!$D6)),"","M"&Teams!$D$10&"-"&Teams!$D6)
R4:R13	R4	=IF(OR(ISBLANK(Teams!$D$11),ISBLANK(Teams!$D6)),"","M"&Teams!$D$11&"-"&Teams!$D6)
S4:S13	S4	=IF(OR(ISBLANK(Teams!$D$12),ISBLANK(Teams!$D6)),"","M"&Teams!$D$12&"-"&Teams!$D6)
T4:T13	T4	=IF(OR(ISBLANK(Teams!$D$13),ISBLANK(Teams!$D6)),"","M"&Teams!$D$13&"-"&Teams!$D6)
U4:U13	U4	=IF(OR(ISBLANK(Teams!$D$14),ISBLANK(Teams!$D6)),"","M"&Teams!$D$14&"-"&Teams!$D6)
V4:V13	V4	=IF(OR(ISBLANK(Teams!$D$15),ISBLANK(Teams!$D6)),"","M"&Teams!$D$15&"-"&Teams!$D6)
B15:B24	B15	=IF(OR(ISBLANK(Teams!$C$6),ISBLANK(Teams!$C6)),"","M"&Teams!$C$6&"-"&Teams!$C6)
C15:C24	C15	=IF(OR(ISBLANK(Teams!$C$7),ISBLANK(Teams!$C6)),"","M"&Teams!$C$7&"-"&Teams!$C6)
D15:D24	D15	=IF(OR(ISBLANK(Teams!$C$8),ISBLANK(Teams!$C6)),"","M"&Teams!$C$8&"-"&Teams!$C6)
E15:E24	E15	=IF(OR(ISBLANK(Teams!$C$9),ISBLANK(Teams!$C6)),"","M"&Teams!$C$9&"-"&Teams!$C6)
F15:F24	F15	=IF(OR(ISBLANK(Teams!$C$10),ISBLANK(Teams!$C6)),"","M"&Teams!$C$10&"-"&Teams!$C6)
G15:G24	G15	=IF(OR(ISBLANK(Teams!$C$11),ISBLANK(Teams!$C6)),"","M"&Teams!$C$11&"-"&Teams!$C6)
H15:H24	H15	=IF(OR(ISBLANK(Teams!$C$12),ISBLANK(Teams!$C6)),"","M"&Teams!$C$12&"-"&Teams!$C6)
I15:I24	I15	=IF(OR(ISBLANK(Teams!$C$13),ISBLANK(Teams!$C6)),"","M"&Teams!$C$13&"-"&Teams!$C6)
J15:J24	J15	=IF(OR(ISBLANK(Teams!$C$14),ISBLANK(Teams!$C6)),"","M"&Teams!$C$14&"-"&Teams!$C6)
K15:K24	K15	=IF(OR(ISBLANK(Teams!$C$15),ISBLANK(Teams!$C6)),"","M"&Teams!$C$15&"-"&Teams!$C6)
M15:M24	M15	=IF(OR(ISBLANK(Teams!$E$6),ISBLANK(Teams!$E6)),"","M"&Teams!$E$6&"-"&Teams!$E6)
N15:N24	N15	=IF(OR(ISBLANK(Teams!$E$7),ISBLANK(Teams!$E6)),"","M"&Teams!$E$7&"-"&Teams!$E6)
O15:O24	O15	=IF(OR(ISBLANK(Teams!$E$8),ISBLANK(Teams!$E6)),"","M"&Teams!$E$8&"-"&Teams!$E6)
P15:P24	P15	=IF(OR(ISBLANK(Teams!$E$9),ISBLANK(Teams!$E6)),"","M"&Teams!$E$9&"-"&Teams!$E6)
Q15:Q24	Q15	=IF(OR(ISBLANK(Teams!$E$10),ISBLANK(Teams!$E6)),"","M"&Teams!$E$10&"-"&Teams!$E6)
R15:R24	R15	=IF(OR(ISBLANK(Teams!$E$11),ISBLANK(Teams!$E6)),"","M"&Teams!$E$11&"-"&Teams!$E6)
S15:S24	S15	=IF(OR(ISBLANK(Teams!$E$12),ISBLANK(Teams!$E6)),"","M"&Teams!$E$12&"-"&Teams!$E6)
T15:T24	T15	=IF(OR(ISBLANK(Teams!$E$13),ISBLANK(Teams!$E6)),"","M"&Teams!$E$13&"-"&Teams!$E6)
U15:U24	U15	=IF(OR(ISBLANK(Teams!$E$14),ISBLANK(Teams!$E6)),"","M"&Teams!$E$14&"-"&Teams!$E6)
V15:V24	V15	=IF(OR(ISBLANK(Teams!$E$15),ISBLANK(Teams!$E6)),"","M"&Teams!$E$15&"-"&Teams!$E6)

Sheet 3 (not complete)

Tournament Best Bracket Formula.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	35		1		M1-2	2		This sheet counts the amount of times people play together. In G2 you can find that player 1-2 play together 2 times.
3			2		M1-3	2
4			3		M1-4	1			Amount of participating players (fill-in)
5			4		M1-5	1
6			5		M1-6	2
7			6		M1-7	1
8			7		M1-8	3
9			8		M1-9	1
10			9		M1-10	3
11			10		M1-11	1
12			11		M1-12	3
13			12		M1-13	0
14			13		M1-14	0
15			14		M1-15	0
16			15		M1-16	0
17			16		M1-17	0
18			17		M1-18	1
19			18		M1-19	3
20			19		M1-20	1
21			20		M1-21	2
22			21		M1-22	0
23			22		M1-23	3
24			23		M1-24	1
25			24		M1-25	0
26			25		M1-26	0
27			26		M1-27	0
28			27		M1-28	1
29			28		M1-29	2
30			29		M1-30	1
31			30		M1-31	0
32			31		M1-32	2
33			32		M1-33	0
34			33		M1-34	2
35			34		M1-35	1
36			35
Match-ups counter

Cell Formulas
Range	Formula
F2:F36	F2	=IF(ROW(A1)<$B$2,"M"&$D$2&"-"&D3,"")
G2:G36	G2	=IF(F2="","",COUNTIF('Overview match-ups'!$B$4:$FI$24,'Match-ups counter'!F2))
D2:D36	D2	=IF(ROW(A1)<=$B$2,ROW(A1),"")

Sheet 4 (not complete)

Tournament Best Bracket Formula.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	In 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
4	0x together	1x together	2x together	3x together	4x together	5x together		0x together	1x together	2x together	3x together	4x together	5x together
5	M1-13	M1-4	M1-2	M1-8	#CALC!	#CALC!		188	209	123	75	0	0		1376
6	M1-14	M1-5	M1-3	M1-10				0	1	4	9	16	25
7	M1-15	M1-7	M1-6	M1-12												Final score
8	M1-16	M1-9	M1-21	M1-19
9	M1-17	M1-11	M1-29	M1-23
10	M1-22	M1-18	M1-32	M2-4
11	M1-25	M1-20	M1-34	M2-6
12	M1-26	M1-24	M2-3	M2-13
13	M1-27	M1-28	M2-7	M2-28
14	M1-31	M1-30	M2-8	M3-5
15	M1-33	M1-35	M2-15	M3-27
16	M2-12	M2-5	M2-17	M3-29
17	M2-16	M2-9	M2-19	M4-5
18	M2-18	M2-10	M2-26	M4-6
19	M2-21	M2-11	M2-30	M4-13
20	M2-22	M2-14	M3-9	M4-17
21	M2-23	M2-20	M3-14	M4-28
22	M2-24	M2-25	M3-16	M4-30
23	M2-27	M2-29	M3-18	M5-14
24	M2-32	M2-31	M3-20	M5-16
25	M2-33	M3-4	M3-25	M6-15
26	M2-34	M3-6	M4-15	M6-30
27	M2-35	M3-7	M4-22	M7-9
Scoring match-ups

Cell Formulas
Range	Formula
B5:B192	B5	=FILTER('Match-ups counter'!F2:F1771,'Match-ups counter'!G2:G1771=0)
C5:C213	C5	=FILTER('Match-ups counter'!F2:F1771,'Match-ups counter'!G2:G1771=1)
D5:D127	D5	=FILTER('Match-ups counter'!F2:F1771,'Match-ups counter'!G2:G1771=2)
E5:E79	E5	=FILTER('Match-ups counter'!F2:F1771,'Match-ups counter'!G2:G1771=3)
F5	F5	=FILTER('Match-ups counter'!F2:F1771,'Match-ups counter'!G2:G1771=4)
G5	G5	=FILTER('Match-ups counter'!F2:F1771,'Match-ups counter'!G2:G1771=5)
I5:N5	I5	=COUNTIF(B5:B1048576,"*")
I6	I6	=0^2
J6	J6	=1^2
K6	K6	=2^2
L6	L6	=3^2
M6	M6	=4^2
N6	N6	=5^2
P5	P5	=I5*I6+J5*J6+K5*K6+L5*L6+M5*M6+N5*N6
Dynamic array formulas.

With kind regards,
Brockspook

 

[Eric W]

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:

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

Use our free round robin tournament generator to create your single round robin, double round robin or play with all schedule for your tournaments and leagues.

golfsoftware.com

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]

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]

That site does not yield best results.

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

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

 

[Brockenspook]

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]

If you are a rocket scientist, this may help. It explains the formulas used for the social golfer problem

 

[johnnyL]

If you speak any other programming languages, you might look here for some coding examples.