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

DIG3787

New Member
Joined
Sep 7, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRST
1MONTHLY MEDAL POINTS
2Apr-21May-21Jul-21Aug-21Sep-21Oct-21Nov-21Jan-22Feb-22POINTS TOTALNUMBER OF EVENTS PLAYEDBEST 6 RESULTSAVERAGE POINTS PER EVENT (Tied Result 1)NUMBER OF VICTORIES (Tied Result 2)
3
4NAMEOVERALL STANDINGS TOUR CHAMPIONSHIP
5Member 1090700000162168.0000
6Member 2000000000000 0POSITIONNAME
7Member 30006000006166.00001Member 1
8Member 401000000001011010.00012Member 10
9Member 50700000007177.00003Member 4
10Member 60800000008188.00004Member 4Should be Member 17
11Member 7050500000102105.00005Member 4Should be Member 7
12Member 8000000000000 0
13Member 9000000000000 0
14Member 10060800000142147.0000
15Member 110304000007273.5000
16Member 12000000000000 0
17Member 130400000004144.0000
18Member 14000000000000 0
19Member 150009000009199.0000
20Member 16000000000000 0
21Member 1700010000001011010.0001
22Member 180003000003133.0000
23Member 19000000000000 0
24Member 20000000000000 0
OVERALL STANDINGS
Cell Formulas
RangeFormula
K5:K24K5=SUM(B5:J5)
L5:L24L5=COUNTIF(B5:J5,"<>0")
M5:M24M5=SUM(LARGE(B5:J5,{1,2,3,4,5,6}))
N5:N24N5=IF(L5<>0,(SUM(K5/L5)),"")
O5:O24O5=COUNTIF(B5:J5,"10")+COUNTIF(B5:J5,"20")
S7S7=INDEX(A5:A34,MATCH(LARGE(M5:M34,1),M5:M34,0))
S8S8=INDEX(A5:A34,MATCH(LARGE(M5:M34,2),M5:M34,0))
S9S9=INDEX(A5:A34,MATCH(LARGE(M5:M34,3),M5:M34,0))
S10S10=INDEX(A5:A34,MATCH(LARGE(M5:M34,4),M5:M34,0))
S11S11=INDEX(A5:A34,MATCH(LARGE(M5:M34,5),M5:M34,0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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.
 
Upvote 0
@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.

Cell Formulas
RangeFormula
K5:K24K5=SUM(B5:J5)
L5:L24L5=COUNTIF(B5:J5,"<>0")
M5:M24M5=SUM(LARGE(B5:J5,{1,2,3,4,5,6}))
N5:N24N5=IF(L5<>0,(SUM(K5/L5)),"")
O5:O24O5=COUNTIF(B5:J5,"10")+COUNTIF(B5:J5,"20")
P5P5=IFERROR(M5-(RANK(N5,$N$5:$N$24,0)/100)-(RANK(O5,$O$5:O$24,0)/1000),"")
Q5:Q24Q5=P5-(COUNTIF(P$5:P5,P5)/10000)
R5:R24R5=RANK(P5,$P$5:$P$34,0) & IF(COUNTIF(P$5:P$34,P5)>1," Tied","")
P6:P24P6=IFERROR(M6-(RANK(N6,$N$5:$N$24,0)/100)-(RANK(O6,$O$5:O$24,0)/1000),0)
S7:S16S7=INDEX($R$5:$R$34,MATCH(LARGE($Q$5:$Q$34,ROWS(S$7:S7)),$Q$5:$Q$34,0))
T7:T16T7=INDEX($A$5:$A$34,MATCH(LARGE($Q$5:$Q$34,ROWS(S$7:S7)),$Q$5:$Q$34,0))


Hope that helps.
 
Upvote 0
Solution
@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.
 
Upvote 0
@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.
 
Upvote 0
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
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
4NameSociety HandicapCourse HandicapStokes Received123456789101112131415161718TOTALLast 3 Countback (Tied 1)Last 6 Countback (Tied 2)Last 9 Countback (Tied 3)OVERALL POSITIONCorrect Positions
5
6member 115·3444468623546466111753193474.9890074.98890109
7member 21·23333454345454445437012243869.9670069.9669022
8member 316·63343454345454445427011233769.9780069.9779031
9member 415·03343454345454445447213253971.9350071.9349045
10member 51·14343454345454445457414264073.9010073.9009078
11member 618·05343454345454445457514264074.9010074.90090910
12member 718·03343454345454545437212253971.9650071.9649054
13member 84343443246454445437012243969.9670069.9668013
14member 97·03343454445454445447313253972.9350072.9349066
15member 1024·04343454445454445447413253973.9350073.9349087
16member 114343454445454445467615274175.8790075.878901111
Station Champs Rd 1 & 2 Totals
Cell Formulas
RangeFormula
Z6:Z16Z6=SUM(W6,V6,U6)
AA6:AA16AA6=SUM(W6,V6,U6,T6,S6,R6)
AB6:AB16AB6=SUM(W6,V6,U6,T6,S6,R6,Q6,P6,O6)
AC6:AC16AC6=IFERROR(X6-(RANK(Z6,$Z$6:$Z$16,1)/100)-(RANK(AA6,$AA$6:$AA$16,1)/1000),0)
AD6:AD16AD6=AC6-(COUNTIF(AC$6:AC6,AC6)/10000)
AE6:AE16AE6=RANK(AD6,$AD$6:$AD$16,1) & IF(COUNTIF(AD$6:AD$16,AD6)>1," Tied","")
X6:X16X6=SUM(F6:W6)
 
Upvote 0
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
 
Upvote 0
@DIG3787 Might be worth checking that your new solution is splitting correctly if it goes to the back 9 ?

Book1
WXYZAAABACADAE
3
418TOTALLast 3 Countback (Tied 1)Last 6 Countback (Tied 2)Last 9 Countback (Tied 3)OVERALL POSITION
5
61753193475.01119
737012243870.03332
827011233770.02221
947213253972.06545
1057414264074.08887 Tied
1157514264075.088810
1237212253972.03544
1337012243970.03343
1457414264074.08887 Tied
1547413253974.06546
1667615274176.122111
17
Sheet2
Cell Formulas
RangeFormula
Z6:Z16Z6=SUM(U6:W6)
AA6:AA16AA6=SUM(R6:W6)
AB6:AB16AB6=SUM(O6:W6)
AC6:AC16AC6=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:X16X6=SUM(F6:W6)
AE6:AE16AE6=RANK(AC6,$AC$6:$AC$16,1) & IF(COUNTIF(AC$6:AC$16,AC6)>1," Tied","")
 
Upvote 0

Forum statistics

Threads
1,223,152
Messages
6,170,389
Members
452,324
Latest member
stuart1980

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top