Formula to find top rated

Davebro

Board Regular
Joined
Feb 22, 2018
Messages
81
Office Version
  1. 2016
Platform
  1. Windows
Trying to find the top rating from each different time (
12:15:00​
Ballintoy Harbour)
155​

155​
dailyreport-2020-08-14.xls
ABC
112:15:00Secret Handsheikh149
212:15:00Ancient Times104
312:15:00Arceus1
412:15:00Ballyconneely Bay1
512:15:00Camerily Joe87
612:15:00Danzart152
712:15:00Mr Lauda118
812:15:00Prospect1
912:15:00Able Love1
1012:15:00Ballintoy Harbour155
1112:45:00Carribean Queen1
1212:45:00Favourite Niece111
1312:45:00Gangster Girl90
1412:45:00International Lady1
1512:45:00Keeping Secrets135
1612:45:00Kingmania122
1712:45:00Kornflake1
1812:45:00Maeves Memory92
1912:45:00Meu Amor156
2012:45:00Midnight Poppy1
2112:45:00Stockingfiller1
2212:45:00Three Hat Day96
2312:45:00Waitingonanalibi1
2412:45:00Zahrisa128
2513:00:00Acclamadic2
2613:00:00Baron Wild1
2713:00:00Britzka0
2813:00:00Trusted Ally141
2913:00:00Kalu Pande2
3013:00:00Ramon Di Loria1
3113:00:00A Case Of You2
3213:00:00Iron Sheriff142
3313:00:00Let Me Pass1
3413:00:00Rich Law No Law113
3513:00:00The Last Knight141
3613:00:00A Taad Moody2
3713:00:00Notoriously Risky1
3813:00:00Charterhouse198
3913:00:00Feeling Tip Top2
4013:00:00Men Of Dreams2
4113:00:00Even Flow2
4213:00:00Allagar173
4313:00:00Stitch Pickings104
4413:00:00Knocknakilla94
4513:00:00Breagagh88
4613:15:00War Whisper150
4713:15:00Inexes141
4813:15:00Airglow128
4913:15:00Epeius150
5013:15:00Lucky Lucky Man119
5113:15:00Foreshore151
5213:15:00Dream Together135
5313:15:00Singe Anglais143
5413:15:00Yukon Mission142
5513:15:00Knightcap138
5613:15:00Round The Island116
5713:30:00Talacre174
5813:30:00Connla1
5913:30:00Nepali1
6013:30:00Rosscarbery (GER)1
6113:30:00Rugadh Realta1
6213:30:00Shahaada1
6313:30:00Shandra2
6413:30:00Ubuntu1
6513:40:00Elland Road Boy1
6613:40:00Haseef96
6713:40:00Red Walls0
6813:40:00Sands In Time1
6913:40:00Shem147
7013:40:00The Flying Ginger2
7113:45:00Mr Orange143
7213:45:00Penny Pot Lane100
7313:45:00Jordan Electrics144
7413:45:00Coastal Mist131
7513:45:00Electric Mistress179
7613:45:00What A Business131
7713:45:00Jungle Inthebungle132
7813:45:00Araifjan136
7913:45:00Lincoln Gamble151
8013:45:00Only Alone141
8113:45:00Castlehill Retreat113
8214:00:00Chief Little Hawk202
8314:00:00Eastern Voice180
8414:00:00Hyde Park Barracks153
8514:00:00Frenetic236
8614:00:00All Things Bright102
8714:00:00Blue Cabochon172
8814:00:00Moon In Her Eye148
8914:00:00Sweet Gardenia116
9014:00:00Tilia Cordata1
9114:10:00Hot Scoop182
9214:10:00London Palladium143
9314:10:00Sands Of Time155
9414:10:00Bergerac157
9514:10:00Gumdrop117
9614:10:00Merry Secret132
9714:10:00Cuban Breeze185
9814:20:00Star In The Making159
9914:20:00Oakenshield147
10014:20:00Abstemious152
10114:20:00Treble Treble132
10214:20:00Breath Of Joy185
10314:20:00Stone Soldier147
10414:20:00Phuket Power154
10514:20:00Many A Star173
10614:20:00Hurcle139
10714:20:00Melody King153
10814:20:00Benefit Street170
10914:20:00Custodian118
11014:20:00Auckland Lodge126
11114:30:00Aircraft Carrier119
11214:30:00Broad Street171
11314:30:00Giuseppe Garibaldi153
11414:30:00Master Of Reality223
11514:30:00Micro Manage159
11614:30:00Sea The Lion171
11714:30:00Maria Christina140
11814:30:00Search For A Song230
11914:30:00Delphi264
12014:30:00Monument Valley189
12114:40:00Spirit Of Sisra146
12214:40:00Chase The Dollar162
12314:40:00Calcutta Cup154
12414:40:00Topper Bill159
12514:40:00Scots Gold106
12614:50:00Burmese Waltz213
12714:50:00Natalies Joy149
12814:50:00Shades Of Blue242
12914:50:00Caspian Queen153
13014:50:00Crispina150
13114:50:00Exceptional212
13214:50:00Jouska179
13314:50:00Lambeth Walk213
13414:50:00Magical Journey192
13514:50:00Mountain Brave184
13614:50:00Stormy Girl176
13715:00:00Leo De Fury228
13815:00:00Numerian212
13915:00:00Rakan193
14015:00:00Sinawann267
14115:00:00Armory247
14215:00:00Degraves204
14315:00:00Helvic Dream196
Sheet1
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

jon1315

New Member
Joined
Jun 16, 2020
Messages
45
Office Version
  1. 365
Platform
  1. Windows
Hi
Do you mean something like the screenshot below?

Thanks
Jon
 

Attachments

  • Capture.PNG
    Capture.PNG
    61.8 KB · Views: 9

jon1315

New Member
Joined
Jun 16, 2020
Messages
45
Office Version
  1. 365
Platform
  1. Windows
Hi
Do you mean something like the screenshot below?

Thanks
Jon
 

jon1315

New Member
Joined
Jun 16, 2020
Messages
45
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

No problem - glad I could help
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
that is exactly what I am looking for.
Is it possible to have more than one equal top rated? If so, what result do you want?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, IF you have Excel 365 you could use this to get the column I list of times (only needs to be entered into I1 and the other results will automatically 'spill' down the column) and all the top-rated values for each time (only needs to be entered in J1 and copied down and the other results will automatically 'spill' across to other columns.

Note that I have changed a few of the column C values so that there some equals

20 08 14.xlsm
ABCDEFGHIJKL
112:15:00Secret Handsheikh149.0012:15:00ArceusBallintoy Harbour
212:15:00Ancient Times104.0012:45:00Meu Amor
312:15:00Arceus155.0013:00:00Trusted AllyThe Last KnightCharterhouse
412:15:00Ballyconneely Bay1.0013:15:00Foreshore
512:15:00Camerily Joe87.0013:30:00Talacre
612:15:00Danzart152.0013:40:00Shem
712:15:00Mr Lauda118.0013:45:00Electric Mistress
812:15:00Prospect1.0014:00:00Frenetic
912:15:00Able Love1.0014:10:00Cuban Breeze
1012:15:00Ballintoy Harbour155.0014:20:00Breath Of Joy
1112:45:00Carribean Queen1.0014:30:00Delphi
1212:45:00Favourite Niece111.0014:40:00Chase The Dollar
1312:45:00Gangster Girl90.0014:50:00Shades Of Blue
1412:45:00International Lady1.0015:00:00Sinawann
1512:45:00Keeping Secrets135.00
1612:45:00Kingmania122.00
1712:45:00Kornflake1.00
1812:45:00Maeves Memory92.00
1912:45:00Meu Amor156.00
2012:45:00Midnight Poppy1.00
2112:45:00Stockingfiller1.00
2212:45:00Three Hat Day96.00
2312:45:00Waitingonanalibi1.00
2412:45:00Zahrisa128.00
2513:00:00Acclamadic2.00
2613:00:00Baron Wild1.00
2713:00:00Britzka0.00
2813:00:00Trusted Ally198.00
2913:00:00Kalu Pande2.00
3013:00:00Ramon Di Loria1.00
3113:00:00A Case Of You2.00
3213:00:00Iron Sheriff142.00
3313:00:00Let Me Pass1.00
3413:00:00Rich Law No Law113.00
3513:00:00The Last Knight198.00
3613:00:00A Taad Moody2.00
3713:00:00Notoriously Risky1.00
3813:00:00Charterhouse198.00
3913:00:00Feeling Tip Top2.00
4013:00:00Men Of Dreams2.00
4113:00:00Even Flow2.00
4213:00:00Allagar173.00
4313:00:00Stitch Pickings104.00
4413:00:00Knocknakilla94.00
4513:00:00Breagagh88.00
4613:15:00War Whisper150.00
4713:15:00Inexes141.00
4813:15:00Airglow128.00
4913:15:00Epeius150.00
5013:15:00Lucky Lucky Man119.00
5113:15:00Foreshore151.00
5213:15:00Dream Together135.00
5313:15:00Singe Anglais143.00
5413:15:00Yukon Mission142.00
5513:15:00Knightcap138.00
5613:15:00Round The Island116.00
5713:30:00Talacre174.00
5813:30:00Connla1.00
5913:30:00Nepali1.00
6013:30:00Rosscarbery (GER)1.00
6113:30:00Rugadh Realta1.00
6213:30:00Shahaada1.00
6313:30:00Shandra2.00
6413:30:00Ubuntu1.00
6513:40:00Elland Road Boy1.00
6613:40:00Haseef96.00
6713:40:00Red Walls0.00
6813:40:00Sands In Time1.00
6913:40:00Shem147.00
7013:40:00The Flying Ginger2.00
7113:45:00Mr Orange143.00
7213:45:00Penny Pot Lane100.00
7313:45:00Jordan Electrics144.00
7413:45:00Coastal Mist131.00
7513:45:00Electric Mistress179.00
7613:45:00What A Business131.00
7713:45:00Jungle Inthebungle132.00
7813:45:00Araifjan136.00
7913:45:00Lincoln Gamble151.00
8013:45:00Only Alone141.00
8113:45:00Castlehill Retreat113.00
8214:00:00Chief Little Hawk202.00
8314:00:00Eastern Voice180.00
8414:00:00Hyde Park Barracks153.00
8514:00:00Frenetic236.00
8614:00:00All Things Bright102.00
8714:00:00Blue Cabochon172.00
8814:00:00Moon In Her Eye148.00
8914:00:00Sweet Gardenia116.00
9014:00:00Tilia Cordata1.00
9114:10:00Hot Scoop182.00
9214:10:00London Palladium143.00
9314:10:00Sands Of Time155.00
9414:10:00Bergerac157.00
9514:10:00Gumdrop117.00
9614:10:00Merry Secret132.00
9714:10:00Cuban Breeze185.00
9814:20:00Star In The Making159.00
9914:20:00Oakenshield147.00
10014:20:00Abstemious152.00
10114:20:00Treble Treble132.00
10214:20:00Breath Of Joy185.00
10314:20:00Stone Soldier147.00
10414:20:00Phuket Power154.00
10514:20:00Many A Star173.00
10614:20:00Hurcle139.00
10714:20:00Melody King153.00
10814:20:00Benefit Street170.00
10914:20:00Custodian118.00
11014:20:00Auckland Lodge126.00
11114:30:00Aircraft Carrier119.00
11214:30:00Broad Street171.00
11314:30:00Giuseppe Garibaldi153.00
11414:30:00Master Of Reality223.00
11514:30:00Micro Manage159.00
11614:30:00Sea The Lion171.00
11714:30:00Maria Christina140.00
11814:30:00Search For A Song230.00
11914:30:00Delphi264.00
12014:30:00Monument Valley189.00
12114:40:00Spirit Of Sisra146.00
12214:40:00Chase The Dollar162.00
12314:40:00Calcutta Cup154.00
12414:40:00Topper Bill159.00
12514:40:00Scots Gold106.00
12614:50:00Burmese Waltz213.00
12714:50:00Natalies Joy149.00
12814:50:00Shades Of Blue242.00
12914:50:00Caspian Queen153.00
13014:50:00Crispina150.00
13114:50:00Exceptional212.00
13214:50:00Jouska179.00
13314:50:00Lambeth Walk213.00
13414:50:00Magical Journey192.00
13514:50:00Mountain Brave184.00
13614:50:00Stormy Girl176.00
13715:00:00Leo De Fury228.00
13815:00:00Numerian212.00
13915:00:00Rakan193.00
14015:00:00Sinawann267.00
14115:00:00Armory247.00
14215:00:00Degraves204.00
14315:00:00Helvic Dream196.00
Top Rated
Cell Formulas
RangeFormula
I1:I14I1=UNIQUE(A1:A143)
J1:K1,J3:L3,J2,J4:J14J1=TRANSPOSE(FILTER(B$1:B$143,(A$1:A$143=I1)*(C$1:C$143=MAXIFS(C$1:C$143,A$1:A$143,I1))))
Dynamic array formulas.
 

Davebro

Board Regular
Joined
Feb 22, 2018
Messages
81
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Many th
12:15:00Secret Handsheikh149.0012:15:00ArceusBallintoy Harbour212:15:00Ancient Times104.0012:45:00Meu Amor312:15:00Arceus155.0013:00:00Trusted AllyThe Last KnightCharterhouse412:15:00Ballyconneely Bay1.0013:15:00Foreshore512:15:00Camerily Joe87.0013:30:00Talacre612:15:00Danzart152.0013:40:00Shem712:15:00Mr Lauda118.0013:45:00Electric Mistress812:15:00Prospect1.0014:00:00Frenetic912:15:00Able Love1.0014:10:00Cuban Breeze1012:15:00Ballintoy Harbour155.0014:20:00Breath Of Joy1112:45:00Carribean Queen1.0014:30:00Delphi1212:45:00Favourite Niece111.0014:40:00Chase The Dollar1312:45:00Gangster Girl90.0014:50:00Shades Of Blue1412:45:00International Lady1.0015:00:00Sinawann1512:45:00Keeping Secrets135.001612:45:00Kingmania122.001712:45:00Kornflake1.001812:45:00Maeves Memory92.001912:45:00Meu Amor156.002012:45:00Midnight Poppy1.002112:45:00Stockingfiller1.002212:45:00Three Hat Day96.002312:45:00Waitingonanalibi1.002412:45:00Zahrisa128.002513:00:00Acclamadic2.002613:00:00Baron Wild1.002713:00:00Britzka0.002813:00:00Trusted Ally198.002913:00:00Kalu Pande2.003013:00:00Ramon Di Loria1.003113:00:00A Case Of You2.003213:00:00Iron Sheriff142.003313:00:00Let Me Pass1.003413:00:00Rich Law No Law113.003513:00:00The Last Knight198.003613:00:00A Taad Moody2.003713:00:00Notoriously Risky1.003813:00:00Charterhouse198.003913:00:00Feeling Tip Top2.004013:00:00Men Of Dreams2.004113:00:00Even Flow2.004213:00:00Allagar173.004313:00:00Stitch Pickings104.004413:00:00Knocknakilla94.004513:00:00Breagagh88.004613:15:00War Whisper150.004713:15:00Inexes141.004813:15:00Airglow128.004913:15:00Epeius150.005013:15:00Lucky Lucky Man119.005113:15:00Foreshore151.005213:15:00Dream Together135.005313:15:00Singe Anglais143.005413:15:00Yukon Mission142.005513:15:00Knightcap138.005613:15:00Round The Island116.005713:30:00Talacre174.005813:30:00Connla1.005913:30:00Nepali1.006013:30:00Rosscarbery (GER)1.006113:30:00Rugadh Realta1.006213:30:00Shahaada1.006313:30:00Shandra2.006413:30:00Ubuntu1.006513:40:00Elland Road Boy1.006613:40:00Haseef96.006713:40:00Red Walls0.006813:40:00Sands In Time1.006913:40:00Shem147.007013:40:00The Flying Ginger2.007113:45:00Mr Orange143.007213:45:00Penny Pot Lane100.007313:45:00Jordan Electrics144.007413:45:00Coastal Mist131.007513:45:00Electric Mistress179.007613:45:00What A Business131.007713:45:00Jungle Inthebungle132.007813:45:00Araifjan136.007913:45:00Lincoln Gamble151.008013:45:00Only Alone141.008113:45:00Castlehill Retreat113.008214:00:00Chief Little Hawk202.008314:00:00Eastern Voice180.008414:00:00Hyde Park Barracks153.008514:00:00Frenetic236.008614:00:00All Things Bright102.008714:00:00Blue Cabochon172.008814:00:00Moon In Her Eye148.008914:00:00Sweet Gardenia116.009014:00:00Tilia Cordata1.009114:10:00Hot Scoop182.009214:10:00London Palladium143.009314:10:00Sands Of Time155.009414:10:00Bergerac157.009514:10:00Gumdrop117.009614:10:00Merry Secret132.009714:10:00Cuban Breeze185.009814:20:00Star In The Making159.009914:20:00Oakenshield147.0010014:20:00Abstemious152.0010114:20:00Treble Treble132.0010214:20:00Breath Of Joy185.0010314:20:00Stone Soldier147.0010414:20:00Phuket Power154.0010514:20:00Many A Star173.0010614:20:00Hurcle139.0010714:20:00Melody King153.0010814:20:00Benefit Street170.0010914:20:00Custodian118.0011014:20:00Auckland Lodge126.0011114:30:00Aircraft Carrier119.0011214:30:00Broad Street171.0011314:30:00Giuseppe Garibaldi153.0011414:30:00Master Of Reality223.0011514:30:00Micro Manage159.0011614:30:00Sea The Lion171.0011714:30:00Maria Christina140.0011814:30:00Search For A Song230.0011914:30:00Delphi264.0012014:30:00Monument Valley189.0012114:40:00Spirit Of Sisra146.0012214:40:00Chase The Dollar162.0012314:40:00Calcutta Cup154.0012414:40:00Topper Bill159.0012514:40:00Scots Gold106.0012614:50:00Burmese Waltz213.0012714:50:00Natalies Joy149.0012814:50:00Shades Of Blue242.0012914:50:00Caspian Queen153.0013014:50:00Crispina150.0013114:50:00Exceptional212.0013214:50:00Jouska179.0013314:50:00Lambeth Walk213.0013414:50:00Magical Journey192.0013514:50:00Mountain Brave184.0013614:50:00Stormy Girl176.0013715:00:00Leo De Fury228.0013815:00:00Numerian212.0013915:00:00Rakan193.0014015:00:00Sinawann267.0014115:00:00Armory247.0014215:00:00Degraves204.0014315:00:00Helvic Dream196.00
Many thanks for your advice. I have updated my details also.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
I have updated my details also.
Thanks for that. (y)

I take it then that it is not possible to have more than one equal top-rated item for a particular time?
.. or that you are happy to just have the first one if that happens?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
that is exactly what I am looking for.
Since you do not have Excel 365 you obviously can't use the previous suggestion I made but I have now looked more closely at the previous suggestion and note that it may not always produce the result you want/expect. If you look at my small sample below, the suggested formula (column J) produces the incorrect result in cell J3. I have provided a more robust alternative in column K that you may wish to consider instead. Adjust the row ranges to suit your own data of course but it is generally best to avoid whole column references in calculation functions.

20 08 14.xlsm
ABCDEFGHIJK
112:15:00Secret Handsheikh149.0012:15:00Ballintoy HarbourBallintoy Harbour
212:15:00Ancient Times104.0012:45:00Favourite NieceFavourite Niece
312:15:00Able Love1.0013:00:00Ballintoy HarbourAcclamadic
412:15:00Ballintoy Harbour155.00
512:45:00Carribean Queen1.00
612:45:00Favourite Niece111.00
712:45:00Three Hat Day96.00
812:45:00Waitingonanalibi1.00
912:45:00Zahrisa108.00
1013:00:00Acclamadic155.00
1113:00:00Baron Wild108.00
12
Top Rated
Cell Formulas
RangeFormula
J1:J3J1=INDEX(C:C,MATCH(MAXIFS(D:D,B:B,I1),D:D,0))
K1:K3K1=INDEX(C:C,AGGREGATE(15,6,ROW(C$1:C$100)/((B$1:B$100=I1)*(D$1:D$100=MAXIFS(D$1:D$100,B$1:B$100,I1))),1))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,462
Members
414,451
Latest member
jrose7

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
Top