Tournament Setup for 8 Teams. How to setup for losers bracket.

[TerrorTot38]

Hi All, I have a knockout tournament for 16 players. When you lose a match you are out the first round just puts the players in the firstbox

=CONCATENATE(B19, " & ", B20)

The second box (semi final) has the statement

=IFERROR(VLOOKUP(TRUE,A2:B3,2,FALSE),"")

. Then I have a final box with the same code as the semi final.

	Top 8			Semi-Final		Final		Winner
TRUE​	A & B
FALSE​	C & D
			FALSE​	A & B
			TRUE​	G & H
FALSE​	E & F
TRUE​	G & H
						FALSE​	G & H		I & J
						TRUE​	I & J
TRUE​	I & J
FALSE​	K & L
			TRUE​	I & J
			FALSE​	M & N
TRUE​	M & N
FALSE​	O & P

My question in the example above is C & D, E & F, K & L , O & P all lost. How would I generate something similar for the losers bracket. Alternatively Random pairings / opponents for example D & E, F & K, L & P, O & C which I could hard code but what if C & D won but A & B lost. Is there such a way to do this?

Any questions please do ask.

Thanks,
J

 

[BSALV]

you could do it like this (i did 2 methods, one with nested if's and one with vlookup)
the green cells are 1 when home wins and 0 when away wins, the other adjust itselfs.
The losers play then there own tournament, see N21:N24.
There you copy those random numbers 1-4 from the column M into N, or you do it your own way.

Map1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	Top 8			Semi-Final		Final		Winner
3	1	A & B
4	0	C & D
5				1	A & B
6					G & H
7	0	E & F
8	1	G & H
9							1	A & B		A & B
10								I & J
11	1	I & J
12	0	K & L
13				1	I & J
14					M & N
15	1	M & N
16	0	O & P
17
18
19				1	O & P
20				0	C & D								random 1-4	copy column M and paste as value in N
21							1	O & P		O & P		0,630392	3	2	C & D
22								K & L				0,86111	2	4	E & F
23				1	K & L							0,969131	1	3	K & L
24				0	E & F							0,05094	4	1	O & P
25
Blad1

Cell Formulas
Range	Formula
A4,D24,D20,A16,A12,A8	A4	=+IF(A3<>"",1-A3,"")
E5,H21,E13	E5	=IF(A3<>"",IF(A3=1,B3,B4),"")
E6,H22,E14	E6	=IF(A7<>"",IF(A7=1,B7,B8),"")
H9	H9	=IF(D5<>"",IF(D5=1,E5,E6),"")
J9,J21	J9	=IF(G9<>"",IF(G9=1,H9,H10),"")
E19	E19	=VLOOKUP(1,$N$21:$O$24,2,0)
E20	E20	=VLOOKUP(2,$N$21:$O$24,2,0)
L21:L24	L21	=RAND()
M21:M24	M21	=RANK(L21,$L$21:$L$24)
E23	E23	=VLOOKUP(3,$N$21:$O$24,2,0)
E24	E24	=VLOOKUP(4,$N$21:$O$24,2,0)
O21	O21	=VLOOKUP(0,$A$3:$B$4,2,0)
O22	O22	=VLOOKUP(0,A7:B8,2,0)
O23	O23	=VLOOKUP(0,$A$11:$B$12,2,0)
O24	O24	=VLOOKUP(0,A15:B16,2,0)

Cells with Data Validation
Cell	Allow	Criteria
A3	Whole number	between 0 and 1
A7	Whole number	between 0 and 1
A11	Whole number	between 0 and 1
A15	Whole number	between 0 and 1
D13	Whole number	between 0 and 1
D5	Whole number	between 0 and 1
G9	Whole number	between 0 and 1
D19	Whole number	between 0 and 1
D23	Whole number	between 0 and 1
G21	Whole number	between 0 and 1