How to create a tournament generator in Excel

[dekion]

Hi

I have following brain teaser, which I don't see how to start with.

I have 3 groups, every group exist our of 4 teams and I'm looking for a generator that creates 3 fixtures / Team that need to play vs. the other groups.

In example Team 1 from group 1 needs to play 3 games vs. 3 teams picked out of group 2 of 3

Group 1	Group 2	Group 3
Team 1	Team 1	Team 1
Team 2	Team 2	Team 2
Team 3	Team 3	Team 3
Team 4	Team 4	Team 4

Would you have an idea how I can start with this, I'm really not seeing it.

I would really want to avoid VBA & work with standard Excel formulas.

Thank you for pushing me in the (right) direction
Regards

 

[Cubist]

You can try this. Cols E-G gives you all the possible combinations. If you don't want to see it, it can be incorporated all into 1 single formula. Col I-K generates 3 random combinations. Change cell J1 for the team you want to generate for.

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Group 1	Group 2	Group 3		Team 1	Team 2	Team 3		Generate 3 random games for	Team A
2	Team A	Team B	Team C		Team A	Team B	Team C		Team A	Team N	Team O
3	Team E	Team F	Team G		Team A	Team B	Team G		Team A	Team J	Team C
4	Team I	Team J	Team K		Team A	Team B	Team K		Team A	Team B	Team O
5	Team M	Team N	Team O		Team A	Team B	Team O
6					Team A	Team F	Team C
7					Team A	Team F	Team G
8					Team A	Team F	Team K
9					Team A	Team F	Team O
10					Team A	Team J	Team C
11					Team A	Team J	Team G
12					Team A	Team J	Team K
13					Team A	Team J	Team O
14					Team A	Team N	Team C
15					Team A	Team N	Team G
16					Team A	Team N	Team K
17					Team A	Team N	Team O
18					Team E	Team B	Team C
19					Team E	Team B	Team G
20					Team E	Team B	Team K
21					Team E	Team B	Team O
22					Team E	Team F	Team C
23					Team E	Team F	Team G
24					Team E	Team F	Team K
25					Team E	Team F	Team O
26					Team E	Team J	Team C
27					Team E	Team J	Team G
28					Team E	Team J	Team K
29					Team E	Team J	Team O
30					Team E	Team N	Team C
31					Team E	Team N	Team G
32					Team E	Team N	Team K
33					Team E	Team N	Team O
34					Team I	Team B	Team C
35					Team I	Team B	Team G
36					Team I	Team B	Team K
37					Team I	Team B	Team O
38					Team I	Team F	Team C
39					Team I	Team F	Team G
40					Team I	Team F	Team K
41					Team I	Team F	Team O
42					Team I	Team J	Team C
43					Team I	Team J	Team G
44					Team I	Team J	Team K
45					Team I	Team J	Team O
46					Team I	Team N	Team C
47					Team I	Team N	Team G
48					Team I	Team N	Team K
49					Team I	Team N	Team O
50					Team M	Team B	Team C
51					Team M	Team B	Team G
52					Team M	Team B	Team K
53					Team M	Team B	Team O
54					Team M	Team F	Team C
55					Team M	Team F	Team G
56					Team M	Team F	Team K
57					Team M	Team F	Team O
58					Team M	Team J	Team C
59					Team M	Team J	Team G
60					Team M	Team J	Team K
61					Team M	Team J	Team O
62					Team M	Team N	Team C
63					Team M	Team N	Team G
64					Team M	Team N	Team K
65					Team M	Team N	Team O
Sheet2

Cell Formulas
Range	Formula
E2:G65	E2	=TEXTSPLIT(TEXTJOIN(",",,TOCOL(A2:A5 &"|"& TOROW(B2:B5)) &"|"&TOROW(C2:C5)),"|",",")
I2:K4	I2	=TAKE(SORTBY(FILTER(E2#,E2:E65=J1),RANDARRAY(16)),3)
Dynamic array formulas.

 

[Cubist]

A second (more flexible) option.

Book2
	A	B	C	D	E	F	G	H
1	Group 1	Group 2	Group 3		All Combinations		Generate 3 random games for:	Team A
2	Team A	Team B	Team C		Team A|Team B|Team C		Team A|Team F|Team K
3	Team E	Team F	Team G		Team A|Team B|Team G		Team A|Team B|Team C
4	Team I	Team J	Team K		Team A|Team B|Team K		Team A|Team N|Team O
5	Team M	Team N	Team O		Team A|Team B|Team O
6					Team A|Team F|Team C
7					Team A|Team F|Team G
8					Team A|Team F|Team K
9					Team A|Team F|Team O
10					Team A|Team J|Team C
11					Team A|Team J|Team G
12					Team A|Team J|Team K
13					Team A|Team J|Team O
14					Team A|Team N|Team C
15					Team A|Team N|Team G
16					Team A|Team N|Team K
17					Team A|Team N|Team O
18					Team E|Team B|Team C
19					Team E|Team B|Team G
20					Team E|Team B|Team K
21					Team E|Team B|Team O
22					Team E|Team F|Team C
23					Team E|Team F|Team G
24					Team E|Team F|Team K
25					Team E|Team F|Team O
26					Team E|Team J|Team C
27					Team E|Team J|Team G
28					Team E|Team J|Team K
29					Team E|Team J|Team O
30					Team E|Team N|Team C
31					Team E|Team N|Team G
32					Team E|Team N|Team K
33					Team E|Team N|Team O
34					Team I|Team B|Team C
35					Team I|Team B|Team G
36					Team I|Team B|Team K
37					Team I|Team B|Team O
38					Team I|Team F|Team C
39					Team I|Team F|Team G
40					Team I|Team F|Team K
41					Team I|Team F|Team O
42					Team I|Team J|Team C
43					Team I|Team J|Team G
44					Team I|Team J|Team K
45					Team I|Team J|Team O
46					Team I|Team N|Team C
47					Team I|Team N|Team G
48					Team I|Team N|Team K
49					Team I|Team N|Team O
50					Team M|Team B|Team C
51					Team M|Team B|Team G
52					Team M|Team B|Team K
53					Team M|Team B|Team O
54					Team M|Team F|Team C
55					Team M|Team F|Team G
56					Team M|Team F|Team K
57					Team M|Team F|Team O
58					Team M|Team J|Team C
59					Team M|Team J|Team G
60					Team M|Team J|Team K
61					Team M|Team J|Team O
62					Team M|Team N|Team C
63					Team M|Team N|Team G
64					Team M|Team N|Team K
65					Team M|Team N|Team O
Sheet2

Cell Formulas
Range	Formula
E2:E65	E2	=TOCOL(TOCOL(A2:A5&"|"&TOROW(B2:B5))&"|"&TOROW(C2:C5))
G2:G4	G2	=TAKE(SORTBY(FILTER(E2#,ISNUMBER(SEARCH(H1,E2#))),RANDARRAY(16)),3)
Dynamic array formulas.

 

[dekion]

Wow, very nice!

Big thank you!!