Highest runner up

[Andyboi]

Hi all

I'm running a 120 team league. It's 30 groups of 4. The winners of each group qualify to the next stage as do the 2 highest runners up.

I'm looking for a way to create a tab that'll tracks and ranks all of the 2nd placed teams in the 30 groups.

If that makes sense and anyone has any suggestions I'd be grateful

 

[Eric W]

Welcome to the MrExcel forum!

This depends a lot on how your sheet is organized. Please show how each of the teams are listed, how the groups are organized, how the winners and runners-up are chosen, and if we have to worry about ties. But I'm sure we can figure something out. Here's an example of how you can do it:

Book6
	A	B	C	D	E	F	G	H	I	J	K	L
1	Group 1	Score 1	Group 2	Score 2	Group 3	Score 3	Group 4	Score 4	Group 5	Score 5	Group 6	Score 6
2	A1	90	A2	99	A3	74	A4	62	A5	68	A6	88
3	B1	61	B2	54	B3	79	B4	99	B5	61	B6	61
4	C1	53	C2	59	C3	63	C4	57	C5	77	C6	51
5	D1	99	D2	73	D3	63	D4	58	D5	81	D6	84
6
7
8	Winners					Runners-up				Sorted Runners-up
9	Group 1	D1	99			Group 1	A1	90		Group 1	A1	90
10	Group 2	A2	99			Group 2	D2	73		Group 6	D6	84
11	Group 3	B3	79			Group 3	A3	74		Group 5	C5	77
12	Group 4	B4	99			Group 4	A4	62		Group 3	A3	74
13	Group 5	D5	81			Group 5	C5	77		Group 2	D2	73
14	Group 6	A6	88			Group 6	D6	84		Group 4	A4	62
Sheet2

Cell Formulas
Range	Formula
J9:L14	J9	=SORT(F9:H14,3,-1)
B9:C14	B9	=LET(m,MATCH(A9,$A$1:$K$1,0),a,INDEX($A$2:$K$2,0,m):INDEX($A$5:$L$5,0,m+1),INDEX(SORT(a,2,-1),1,{1,2}))
G9:H14	G9	=LET(m,MATCH(F9,$A$1:$K$1,0),a,INDEX($A$2:$K$2,0,m):INDEX($A$5:$L$5,0,m+1),INDEX(SORT(a,2,-1),2,{1,2}))
Dynamic array formulas.

 

[Andyboi]

Welcome to the MrExcel forum!

This depends a lot on how your sheet is organized. Please show how each of the teams are listed, how the groups are organized, how the winners and runners-up are chosen, and if we have to worry about ties. But I'm sure we can figure something out. Here's an example of how you can do it:

Book6
	A	B	C	D	E	F	G	H	I	J	K	L
1	Group 1	Score 1	Group 2	Score 2	Group 3	Score 3	Group 4	Score 4	Group 5	Score 5	Group 6	Score 6
2	A1	90	A2	99	A3	74	A4	62	A5	68	A6	88
3	B1	61	B2	54	B3	79	B4	99	B5	61	B6	61
4	C1	53	C2	59	C3	63	C4	57	C5	77	C6	51
5	D1	99	D2	73	D3	63	D4	58	D5	81	D6	84
6
7
8	Winners					Runners-up				Sorted Runners-up
9	Group 1	D1	99			Group 1	A1	90		Group 1	A1	90
10	Group 2	A2	99			Group 2	D2	73		Group 6	D6	84
11	Group 3	B3	79			Group 3	A3	74		Group 5	C5	77
12	Group 4	B4	99			Group 4	A4	62		Group 3	A3	74
13	Group 5	D5	81			Group 5	C5	77		Group 2	D2	73
14	Group 6	A6	88			Group 6	D6	84		Group 4	A4	62
Sheet2

Cell Formulas
Range	Formula
J9:L14	J9	=SORT(F9:H14,3,-1)
B9:C14	B9	=LET(m,MATCH(A9,$A$1:$K$1,0),a,INDEX($A$2:$K$2,0,m):INDEX($A$5:$L$5,0,m+1),INDEX(SORT(a,2,-1),1,{1,2}))
G9:H14	G9	=LET(m,MATCH(F9,$A$1:$K$1,0),a,INDEX($A$2:$K$2,0,m):INDEX($A$5:$L$5,0,m+1),INDEX(SORT(a,2,-1),2,{1,2}))
Dynamic array formulas.

Hi thanks for taking the time to reply.

This is the way i have the sheets organised at the moment. There will be 30 groups like this. After the league stage the tournament moves onto a knockout stage that needs 32 teams, The 30 group winners and the highest up 2 runners up. I'd love to have it set up that it collates this information for me as previously i would just go through the 30 groups and manually find the highest runners up.

I'm a little new to excel so i may be missing something obvious and guidance would be greatly received.

 

[Andyboi]

Hi thanks for taking the time to reply.

View attachment 114952


This is the way i have the sheets organised at the moment. There will be 30 groups like this. After the league stage the tournament moves onto a knockout stage that needs 32 teams, The 30 group winners and the highest up 2 runners up. I'd love to have it set up that it collates this information for me as previously i would just go through the 30 groups and manually find the highest runners up.

I'm a little new to excel so i may be missing something obvious and guidance would be greatly received.

I should also specify. Group / section winners are determined as follows

"At the end of sectional play if the games won are equal then the shot difference will determine the winner of that section. If the shot difference is the same, then shots “for” will be used. If both shot difference and shots for are the same then we will decide a winner on the number of ends won across all games (not tracked on the excel doc, this is rare and will be checked manually if needed). Winner will be the team who top the group at the conclusion of all matches. Section winners plus 2 highest runners up from all sections, qualify for knockout stages (last 32)."

 

[Eric W]

I'm assuming that all of the section sheets are laid out the same, AND that the G2:O6 table is already sorted by the proper order (winner in first row, runner-up second row, etc.) If so, then you can create a summary sheet like this:

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Sheets		Winners					Runners-up					Sorted Runners-up
2			Team	Wins	Diff.	For		Team	Wins	Diff.	For		Sheet	Team	Wins	Diff.	For
3	Section 1		Team C	2	5	22		Team B	2	4	32		Section 3	Whales	2	5	31
4	Section 2		Beta	2	5	22		Gamma	1	6	34		Section 1	Team B	2	4	32
5	Section 3		Sharks	2	6	23		Whales	2	5	31		Section 2	Gamma	1	6	34
6	Section 4		Bobcats	3	6	24		Lions	1	3	22		Section 4	Lions	1	3	22
7
8
Summary

Cell Formulas
Range	Formula
M3:M6	M3	=SORTBY(A3:A6,I3:I6,-1,J3:J6,-1,K3:K6,-1)
N3:Q6	N3	=SORT(H3:K6,{2,3,4},{-1,-1,-1})
C3:F6	C3	=INDEX(INDIRECT("'"&A3&"'!G3:O3"),{1,3,8,6})
H3:K6	H3	=INDEX(INDIRECT("'"&A3&"'!G4:O4"),{1,3,8,6})
Dynamic array formulas.

It's very tough, and often impossible, to create a spill formula that references multiple sheets. So I had to list the sheet names on the left. (I only created 4 test sheets, you'll need to put all 30 sheet names.) Then you can put in the C3 and H3 formulas and drag them down for the entire 30 rows. The M3 and N3 formulas are spill formulas, you'll need to update the range references to your sheet (change the bottom row of the ranges to 32 instead of 6). You can also sort the winners the same way if you want, in case that might help with seeding.

 

[Andyboi]

I'm assuming that all of the section sheets are laid out the same, AND that the G2:O6 table is already sorted by the proper order (winner in first row, runner-up second row, etc.) If so, then you can create a summary sheet like this:

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Sheets		Winners					Runners-up					Sorted Runners-up
2			Team	Wins	Diff.	For		Team	Wins	Diff.	For		Sheet	Team	Wins	Diff.	For
3	Section 1		Team C	2	5	22		Team B	2	4	32		Section 3	Whales	2	5	31
4	Section 2		Beta	2	5	22		Gamma	1	6	34		Section 1	Team B	2	4	32
5	Section 3		Sharks	2	6	23		Whales	2	5	31		Section 2	Gamma	1	6	34
6	Section 4		Bobcats	3	6	24		Lions	1	3	22		Section 4	Lions	1	3	22
7
8
Summary

Cell Formulas
Range	Formula
M3:M6	M3	=SORTBY(A3:A6,I3:I6,-1,J3:J6,-1,K3:K6,-1)
N3:Q6	N3	=SORT(H3:K6,{2,3,4},{-1,-1,-1})
C3:F6	C3	=INDEX(INDIRECT("'"&A3&"'!G3:O3"),{1,3,8,6})
H3:K6	H3	=INDEX(INDIRECT("'"&A3&"'!G4:O4"),{1,3,8,6})
Dynamic array formulas.

It's very tough, and often impossible, to create a spill formula that references multiple sheets. So I had to list the sheet names on the left. (I only created 4 test sheets, you'll need to put all 30 sheet names.) Then you can put in the C3 and H3 formulas and drag them down for the entire 30 rows. The M3 and N3 formulas are spill formulas, you'll need to update the range references to your sheet (change the bottom row of the ranges to 32 instead of 6). You can also sort the winners the same way if you want, in case that might help with seeding.

Thank you for this!

I've used it to work as imagined. Appreciate the time and effort.

 

[Eric W]

Glad to help! Thanks for the feedback.