Find largest value in column, if matches with another value in column then compare next column to find largest value

[DIG3787]

Good morning all,

I'm fairly new to Excel, so please excuse if this question cannot be completed. I have a worksheet for results (golf society points leaderboard), where column A is a generic list of current members, this is then followed by 8 columns of results, which lead to a total points column (column K), number of rounds played (column L), total points of best 6 results (column M), average points per event (column N), number of victories (column O). I am trying to auto update a leaderboard with the names of members, from column A, dependant on the largest value from column M, if two or more members have the same value here, I would like the compare to then take place on column N and finally column O. At the minute, I have been able to get column M compared to index the members but when values are equal it populates the same member numerous times in the leaderboard.

Hopefully I have explained that well enough. I have uploaded the spreadsheet to help try and understand my query. Again if this cannot be done then apologies for posting.

20210401-Monthly Medal Results_TEST.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		MONTHLY MEDAL POINTS
2		Apr-21	May-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Jan-22	Feb-22	POINTS TOTAL	NUMBER OF EVENTS PLAYED	BEST 6 RESULTS	AVERAGE POINTS PER EVENT (Tied Result 1)	NUMBER OF VICTORIES (Tied Result 2)
3
4	NAME																	OVERALL STANDINGS TOUR CHAMPIONSHIP
5	Member 1	0	9	0	7	0	0	0	0	0	16	2	16	8.000	0
6	Member 2	0	0	0	0	0	0	0	0	0	0	0	0		0			POSITION	NAME
7	Member 3	0	0	0	6	0	0	0	0	0	6	1	6	6.000	0			1	Member 1
8	Member 4	0	10	0	0	0	0	0	0	0	10	1	10	10.000	1			2	Member 10
9	Member 5	0	7	0	0	0	0	0	0	0	7	1	7	7.000	0			3	Member 4
10	Member 6	0	8	0	0	0	0	0	0	0	8	1	8	8.000	0			4	Member 4	Should be Member 17
11	Member 7	0	5	0	5	0	0	0	0	0	10	2	10	5.000	0			5	Member 4	Should be Member 7
12	Member 8	0	0	0	0	0	0	0	0	0	0	0	0		0
13	Member 9	0	0	0	0	0	0	0	0	0	0	0	0		0
14	Member 10	0	6	0	8	0	0	0	0	0	14	2	14	7.000	0
15	Member 11	0	3	0	4	0	0	0	0	0	7	2	7	3.500	0
16	Member 12	0	0	0	0	0	0	0	0	0	0	0	0		0
17	Member 13	0	4	0	0	0	0	0	0	0	4	1	4	4.000	0
18	Member 14	0	0	0	0	0	0	0	0	0	0	0	0		0
19	Member 15	0	0	0	9	0	0	0	0	0	9	1	9	9.000	0
20	Member 16	0	0	0	0	0	0	0	0	0	0	0	0		0
21	Member 17	0	0	0	10	0	0	0	0	0	10	1	10	10.000	1
22	Member 18	0	0	0	3	0	0	0	0	0	3	1	3	3.000	0
23	Member 19	0	0	0	0	0	0	0	0	0	0	0	0		0
24	Member 20	0	0	0	0	0	0	0	0	0	0	0	0		0
OVERALL STANDINGS

Cell Formulas
Range	Formula
K5:K24	K5	=SUM(B5:J5)
L5:L24	L5	=COUNTIF(B5:J5,"<>0")
M5:M24	M5	=SUM(LARGE(B5:J5,{1,2,3,4,5,6}))
N5:N24	N5	=IF(L5<>0,(SUM(K5/L5)),"")
O5:O24	O5	=COUNTIF(B5:J5,"10")+COUNTIF(B5:J5,"20")
S7	S7	=INDEX(A5:A34,MATCH(LARGE(M5:M34,1),M5:M34,0))
S8	S8	=INDEX(A5:A34,MATCH(LARGE(M5:M34,2),M5:M34,0))
S9	S9	=INDEX(A5:A34,MATCH(LARGE(M5:M34,3),M5:M34,0))
S10	S10	=INDEX(A5:A34,MATCH(LARGE(M5:M34,4),M5:M34,0))
S11	S11	=INDEX(A5:A34,MATCH(LARGE(M5:M34,5),M5:M34,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Phuoc]

Try this in S7 and copy down:

=INDEX($A$5:$A$34,MATCH(LARGE($M$5:$M$34+1/ROW($M$5:$M$34),R7),$M$5:$M$34+1/ROW($M$5:$M$34),0))

Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Snakehips]

@DIG3787 my take on this is a little more involved but I think it offers you a complete solution.
It utilises three helper columns which you may hide if you wish.

MrExcelAug.xlsm
	K	L	M	N	O	P	Q	R	S	T
1
2	POINTS TOTAL	NUMBER OF EVENTS PLAYED	BEST 6 RESULTS	AVERAGE POINTS PER EVENT (Tied Result 1)	NUMBER OF VICTORIES (Tied Result 2)	Tie Break Helper	Position Helper	Position
3
4									OVERALL STANDINGS TOUR CHAMPIONSHIP
5	16	2	16	8	0	15.957	15.9569	1
6	0	0	0		0	0	-0.0001	13 Tied	POSITION	NAME
7	6	1	6	6	0	5.917	5.9169	10	1	Member 1
8	10	1	10	10	1	9.989	9.9889	3 Tied	2	Member 10
9	7	1	7	7	0	6.937	6.9369	8	3 Tied	Member 4
10	8	1	8	8	0	7.957	7.9569	7	3 Tied	Member 17
11	10	2	10	5	0	9.907	9.9069	5	5	Member 7
12	0	0	0		0	0	-0.0002	13 Tied	6	Member 15
13	0	0	0		0	0	-0.0003	13 Tied	7	Member 6
14	14	2	14	7	0	13.937	13.9369	2	8	Member 5
15	7	2	7	3.5	0	6.887	6.8869	9	9	Member 11
16	0	0	0		0	0	-0.0004	13 Tied	10	Member 3
17	4	1	4	4	0	3.897	3.8969	11
18	0	0	0		0	0	-0.0005	13 Tied
19	9	1	9	9	0	8.967	8.9669	6
20	0	0	0		0	0	-0.0006	13 Tied
21	10	1	10	10	1	9.989	9.9888	3 Tied
22	3	1	3	3	0	2.877	2.8769	12
23	0	0	0		0	0	-0.0007	13 Tied
24	0	0	0		0	0	-0.0008	13 Tied
25
Overall Standings

Cell Formulas
Range	Formula
K5:K24	K5	=SUM(B5:J5)
L5:L24	L5	=COUNTIF(B5:J5,"<>0")
M5:M24	M5	=SUM(LARGE(B5:J5,{1,2,3,4,5,6}))
N5:N24	N5	=IF(L5<>0,(SUM(K5/L5)),"")
O5:O24	O5	=COUNTIF(B5:J5,"10")+COUNTIF(B5:J5,"20")
P5	P5	=IFERROR(M5-(RANK(N5,$N$5:$N$24,0)/100)-(RANK(O5,$O$5:O$24,0)/1000),"")
Q5:Q24	Q5	=P5-(COUNTIF(P$5:P5,P5)/10000)
R5:R24	R5	=RANK(P5,$P$5:$P$34,0) & IF(COUNTIF(P$5:P$34,P5)>1," Tied","")
P6:P24	P6	=IFERROR(M6-(RANK(N6,$N$5:$N$24,0)/100)-(RANK(O6,$O$5:O$24,0)/1000),0)
S7:S16	S7	=INDEX($R$5:$R$34,MATCH(LARGE($Q$5:$Q$34,ROWS(S$7:S7)),$Q$5:$Q$34,0))
T7:T16	T7	=INDEX($A$5:$A$34,MATCH(LARGE($Q$5:$Q$34,ROWS(S$7:S7)),$Q$5:$Q$34,0))

Hope that helps.

 

[DIG3787]

@Snakechips, thank you very much indeed. This works perfectly and I didn't know that you could use helper columns, which can be hidden from view. Thank you very much.

 

[Snakehips]

@Snakechips, thank you very much indeed. This works perfectly and I didn't know that you could use helper columns, which can be hidden from view. Thank you very much.

You are welcome. Glad we could help.
Using 'helper' columns can simplify what otherwise might become very large, complicated, and resource heavy formulas.
It can sometimes help you to build a formula by formulating various elements in spare columns. Then you may combine them into a single formula or just reference the 'helper' as you wish.

 

[DIG3787]

Hi all,

I'm back again. I have further developed the above excel although for this element I am basing the rank on the smallest value in Column X. If this matches another value in Column X, I need the overall leader to be the one with the lowest value in Column Z, if these again match it then moves to the lowest in Column AA and finally, if still matching, the lowest value in Column AB.

By using previous formula, and changing the ranking order from descending to ascending this almost works but there are still a few that come out in the incorrect position. I have manually placed the correct positions in Column AF. Hopefully this makes sense.

I've tried to do a multiplication rather than division to create unique numbers but no luck.

Station Champs.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
4	Name	Society Handicap	Course Handicap	Stokes Received	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	TOTAL		Last 3 Countback (Tied 1)	Last 6 Countback (Tied 2)	Last 9 Countback (Tied 3)			OVERALL POSITION	Correct Positions
5
6	member 1	15·3			4	4	4	4	6	8	6	2	3	5	4	6	4	6	6	1	1	1	75		3	19	34	74.98900	74.98890	10	9
7	member 2	1·2			3	3	3	3	4	5	4	3	4	5	4	5	4	4	4	5	4	3	70		12	24	38	69.96700	69.96690	2	2
8	member 3	16·6			3	3	4	3	4	5	4	3	4	5	4	5	4	4	4	5	4	2	70		11	23	37	69.97800	69.97790	3	1
9	member 4	15·0			3	3	4	3	4	5	4	3	4	5	4	5	4	4	4	5	4	4	72		13	25	39	71.93500	71.93490	4	5
10	member 5	1·1			4	3	4	3	4	5	4	3	4	5	4	5	4	4	4	5	4	5	74		14	26	40	73.90100	73.90090	7	8
11	member 6	18·0			5	3	4	3	4	5	4	3	4	5	4	5	4	4	4	5	4	5	75		14	26	40	74.90100	74.90090	9	10
12	member 7	18·0			3	3	4	3	4	5	4	3	4	5	4	5	4	5	4	5	4	3	72		12	25	39	71.96500	71.96490	5	4
13	member 8				4	3	4	3	4	4	3	2	4	6	4	5	4	4	4	5	4	3	70		12	24	39	69.96700	69.96680	1	3
14	member 9	7·0			3	3	4	3	4	5	4	4	4	5	4	5	4	4	4	5	4	4	73		13	25	39	72.93500	72.93490	6	6
15	member 10	24·0			4	3	4	3	4	5	4	4	4	5	4	5	4	4	4	5	4	4	74		13	25	39	73.93500	73.93490	8	7
16	member 11				4	3	4	3	4	5	4	4	4	5	4	5	4	4	4	5	4	6	76		15	27	41	75.87900	75.87890	11	11
Station Champs Rd 1 & 2 Totals

Cell Formulas
Range	Formula
Z6:Z16	Z6	=SUM(W6,V6,U6)
AA6:AA16	AA6	=SUM(W6,V6,U6,T6,S6,R6)
AB6:AB16	AB6	=SUM(W6,V6,U6,T6,S6,R6,Q6,P6,O6)
AC6:AC16	AC6	=IFERROR(X6-(RANK(Z6,$Z$6:$Z$16,1)/100)-(RANK(AA6,$AA$6:$AA$16,1)/1000),0)
AD6:AD16	AD6	=AC6-(COUNTIF(AC$6:AC6,AC6)/10000)
AE6:AE16	AE6	=RANK(AD6,$AD$6:$AD$16,1) & IF(COUNTIF(AD$6:AD$16,AD6)>1," Tied","")
X6:X16	X6	=SUM(F6:W6)

 

[DIG3787]

Hello again,

I believe I have solved my issue. I was using minus each ranking column from each other and then looking for the smallest number. I have changed this to add each ranking column together and find the smallest number and this seems to work.

Apologies for asking the question and then solving it.

Thanks

 

[Snakehips]

@DIG3787 Might be worth checking that your new solution is splitting correctly if it goes to the back 9 ?

Book1
	W	X	Y	Z	AA	AB	AC	AD	AE
3
4	18	TOTAL		Last 3 Countback (Tied 1)	Last 6 Countback (Tied 2)	Last 9 Countback (Tied 3)			OVERALL POSITION
5
6	1	75		3	19	34	75.0111		9
7	3	70		12	24	38	70.0333		2
8	2	70		11	23	37	70.0222		1
9	4	72		13	25	39	72.0654		5
10	5	74		14	26	40	74.0888		7 Tied
11	5	75		14	26	40	75.0888		10
12	3	72		12	25	39	72.0354		4
13	3	70		12	24	39	70.0334		3
14	5	74		14	26	40	74.0888		7 Tied
15	4	74		13	25	39	74.0654		6
16	6	76		15	27	41	76.1221		11
17
Sheet2

Cell Formulas
Range	Formula
Z6:Z16	Z6	=SUM(U6:W6)
AA6:AA16	AA6	=SUM(R6:W6)
AB6:AB16	AB6	=SUM(O6:W6)
AC6:AC16	AC6	=IFERROR(X6+(RANK(Z6,$Z$6:$Z$16,1)/100)+(RANK(AA6,$AA$6:$AB$16,1)/1000)+(RANK(AB6,$AB$6:$AB$16,1)/10000),0)
X6:X16	X6	=SUM(F6:W6)
AE6:AE16	AE6	=RANK(AC6,$AC$6:$AC$16,1) & IF(COUNTIF(AC$6:AC$16,AC6)>1," Tied","")