Formula to find top rated

[Davebro]

Trying to find the top rating from each different time (

12:15:00​

Ballintoy Harbour)

155​

155​

dailyreport-2020-08-14.xls
	A	B	C
1	12:15:00	Secret Handsheikh	149
2	12:15:00	Ancient Times	104
3	12:15:00	Arceus	1
4	12:15:00	Ballyconneely Bay	1
5	12:15:00	Camerily Joe	87
6	12:15:00	Danzart	152
7	12:15:00	Mr Lauda	118
8	12:15:00	Prospect	1
9	12:15:00	Able Love	1
10	12:15:00	Ballintoy Harbour	155
11	12:45:00	Carribean Queen	1
12	12:45:00	Favourite Niece	111
13	12:45:00	Gangster Girl	90
14	12:45:00	International Lady	1
15	12:45:00	Keeping Secrets	135
16	12:45:00	Kingmania	122
17	12:45:00	Kornflake	1
18	12:45:00	Maeves Memory	92
19	12:45:00	Meu Amor	156
20	12:45:00	Midnight Poppy	1
21	12:45:00	Stockingfiller	1
22	12:45:00	Three Hat Day	96
23	12:45:00	Waitingonanalibi	1
24	12:45:00	Zahrisa	128
25	13:00:00	Acclamadic	2
26	13:00:00	Baron Wild	1
27	13:00:00	Britzka	0
28	13:00:00	Trusted Ally	141
29	13:00:00	Kalu Pande	2
30	13:00:00	Ramon Di Loria	1
31	13:00:00	A Case Of You	2
32	13:00:00	Iron Sheriff	142
33	13:00:00	Let Me Pass	1
34	13:00:00	Rich Law No Law	113
35	13:00:00	The Last Knight	141
36	13:00:00	A Taad Moody	2
37	13:00:00	Notoriously Risky	1
38	13:00:00	Charterhouse	198
39	13:00:00	Feeling Tip Top	2
40	13:00:00	Men Of Dreams	2
41	13:00:00	Even Flow	2
42	13:00:00	Allagar	173
43	13:00:00	Stitch Pickings	104
44	13:00:00	Knocknakilla	94
45	13:00:00	Breagagh	88
46	13:15:00	War Whisper	150
47	13:15:00	Inexes	141
48	13:15:00	Airglow	128
49	13:15:00	Epeius	150
50	13:15:00	Lucky Lucky Man	119
51	13:15:00	Foreshore	151
52	13:15:00	Dream Together	135
53	13:15:00	Singe Anglais	143
54	13:15:00	Yukon Mission	142
55	13:15:00	Knightcap	138
56	13:15:00	Round The Island	116
57	13:30:00	Talacre	174
58	13:30:00	Connla	1
59	13:30:00	Nepali	1
60	13:30:00	Rosscarbery (GER)	1
61	13:30:00	Rugadh Realta	1
62	13:30:00	Shahaada	1
63	13:30:00	Shandra	2
64	13:30:00	Ubuntu	1
65	13:40:00	Elland Road Boy	1
66	13:40:00	Haseef	96
67	13:40:00	Red Walls	0
68	13:40:00	Sands In Time	1
69	13:40:00	Shem	147
70	13:40:00	The Flying Ginger	2
71	13:45:00	Mr Orange	143
72	13:45:00	Penny Pot Lane	100
73	13:45:00	Jordan Electrics	144
74	13:45:00	Coastal Mist	131
75	13:45:00	Electric Mistress	179
76	13:45:00	What A Business	131
77	13:45:00	Jungle Inthebungle	132
78	13:45:00	Araifjan	136
79	13:45:00	Lincoln Gamble	151
80	13:45:00	Only Alone	141
81	13:45:00	Castlehill Retreat	113
82	14:00:00	Chief Little Hawk	202
83	14:00:00	Eastern Voice	180
84	14:00:00	Hyde Park Barracks	153
85	14:00:00	Frenetic	236
86	14:00:00	All Things Bright	102
87	14:00:00	Blue Cabochon	172
88	14:00:00	Moon In Her Eye	148
89	14:00:00	Sweet Gardenia	116
90	14:00:00	Tilia Cordata	1
91	14:10:00	Hot Scoop	182
92	14:10:00	London Palladium	143
93	14:10:00	Sands Of Time	155
94	14:10:00	Bergerac	157
95	14:10:00	Gumdrop	117
96	14:10:00	Merry Secret	132
97	14:10:00	Cuban Breeze	185
98	14:20:00	Star In The Making	159
99	14:20:00	Oakenshield	147
100	14:20:00	Abstemious	152
101	14:20:00	Treble Treble	132
102	14:20:00	Breath Of Joy	185
103	14:20:00	Stone Soldier	147
104	14:20:00	Phuket Power	154
105	14:20:00	Many A Star	173
106	14:20:00	Hurcle	139
107	14:20:00	Melody King	153
108	14:20:00	Benefit Street	170
109	14:20:00	Custodian	118
110	14:20:00	Auckland Lodge	126
111	14:30:00	Aircraft Carrier	119
112	14:30:00	Broad Street	171
113	14:30:00	Giuseppe Garibaldi	153
114	14:30:00	Master Of Reality	223
115	14:30:00	Micro Manage	159
116	14:30:00	Sea The Lion	171
117	14:30:00	Maria Christina	140
118	14:30:00	Search For A Song	230
119	14:30:00	Delphi	264
120	14:30:00	Monument Valley	189
121	14:40:00	Spirit Of Sisra	146
122	14:40:00	Chase The Dollar	162
123	14:40:00	Calcutta Cup	154
124	14:40:00	Topper Bill	159
125	14:40:00	Scots Gold	106
126	14:50:00	Burmese Waltz	213
127	14:50:00	Natalies Joy	149
128	14:50:00	Shades Of Blue	242
129	14:50:00	Caspian Queen	153
130	14:50:00	Crispina	150
131	14:50:00	Exceptional	212
132	14:50:00	Jouska	179
133	14:50:00	Lambeth Walk	213
134	14:50:00	Magical Journey	192
135	14:50:00	Mountain Brave	184
136	14:50:00	Stormy Girl	176
137	15:00:00	Leo De Fury	228
138	15:00:00	Numerian	212
139	15:00:00	Rakan	193
140	15:00:00	Sinawann	267
141	15:00:00	Armory	247
142	15:00:00	Degraves	204
143	15:00:00	Helvic Dream	196
Sheet1

 

[jon1315]

Hi
Do you mean something like the screenshot below?

Thanks
Jon

 

[jon1315]

Hi
Do you mean something like the screenshot below?

Thanks
Jon

 

[Davebro]

Hi
Do you mean something like the screenshot below?

Thanks
Jon

Many thanks, that is exactly what I am looking for.

 

[jon1315]

No problem - glad I could help

 

[Peter_SSs]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	12:15:00	Secret Handsheikh	149.00						12:15:00	Arceus	Ballintoy Harbour
2	12:15:00	Ancient Times	104.00						12:45:00	Meu Amor
3	12:15:00	Arceus	155.00						13:00:00	Trusted Ally	The Last Knight	Charterhouse
4	12:15:00	Ballyconneely Bay	1.00						13:15:00	Foreshore
5	12:15:00	Camerily Joe	87.00						13:30:00	Talacre
6	12:15:00	Danzart	152.00						13:40:00	Shem
7	12:15:00	Mr Lauda	118.00						13:45:00	Electric Mistress
8	12:15:00	Prospect	1.00						14:00:00	Frenetic
9	12:15:00	Able Love	1.00						14:10:00	Cuban Breeze
10	12:15:00	Ballintoy Harbour	155.00						14:20:00	Breath Of Joy
11	12:45:00	Carribean Queen	1.00						14:30:00	Delphi
12	12:45:00	Favourite Niece	111.00						14:40:00	Chase The Dollar
13	12:45:00	Gangster Girl	90.00						14:50:00	Shades Of Blue
14	12:45:00	International Lady	1.00						15:00:00	Sinawann
15	12:45:00	Keeping Secrets	135.00
16	12:45:00	Kingmania	122.00
17	12:45:00	Kornflake	1.00
18	12:45:00	Maeves Memory	92.00
19	12:45:00	Meu Amor	156.00
20	12:45:00	Midnight Poppy	1.00
21	12:45:00	Stockingfiller	1.00
22	12:45:00	Three Hat Day	96.00
23	12:45:00	Waitingonanalibi	1.00
24	12:45:00	Zahrisa	128.00
25	13:00:00	Acclamadic	2.00
26	13:00:00	Baron Wild	1.00
27	13:00:00	Britzka	0.00
28	13:00:00	Trusted Ally	198.00
29	13:00:00	Kalu Pande	2.00
30	13:00:00	Ramon Di Loria	1.00
31	13:00:00	A Case Of You	2.00
32	13:00:00	Iron Sheriff	142.00
33	13:00:00	Let Me Pass	1.00
34	13:00:00	Rich Law No Law	113.00
35	13:00:00	The Last Knight	198.00
36	13:00:00	A Taad Moody	2.00
37	13:00:00	Notoriously Risky	1.00
38	13:00:00	Charterhouse	198.00
39	13:00:00	Feeling Tip Top	2.00
40	13:00:00	Men Of Dreams	2.00
41	13:00:00	Even Flow	2.00
42	13:00:00	Allagar	173.00
43	13:00:00	Stitch Pickings	104.00
44	13:00:00	Knocknakilla	94.00
45	13:00:00	Breagagh	88.00
46	13:15:00	War Whisper	150.00
47	13:15:00	Inexes	141.00
48	13:15:00	Airglow	128.00
49	13:15:00	Epeius	150.00
50	13:15:00	Lucky Lucky Man	119.00
51	13:15:00	Foreshore	151.00
52	13:15:00	Dream Together	135.00
53	13:15:00	Singe Anglais	143.00
54	13:15:00	Yukon Mission	142.00
55	13:15:00	Knightcap	138.00
56	13:15:00	Round The Island	116.00
57	13:30:00	Talacre	174.00
58	13:30:00	Connla	1.00
59	13:30:00	Nepali	1.00
60	13:30:00	Rosscarbery (GER)	1.00
61	13:30:00	Rugadh Realta	1.00
62	13:30:00	Shahaada	1.00
63	13:30:00	Shandra	2.00
64	13:30:00	Ubuntu	1.00
65	13:40:00	Elland Road Boy	1.00
66	13:40:00	Haseef	96.00
67	13:40:00	Red Walls	0.00
68	13:40:00	Sands In Time	1.00
69	13:40:00	Shem	147.00
70	13:40:00	The Flying Ginger	2.00
71	13:45:00	Mr Orange	143.00
72	13:45:00	Penny Pot Lane	100.00
73	13:45:00	Jordan Electrics	144.00
74	13:45:00	Coastal Mist	131.00
75	13:45:00	Electric Mistress	179.00
76	13:45:00	What A Business	131.00
77	13:45:00	Jungle Inthebungle	132.00
78	13:45:00	Araifjan	136.00
79	13:45:00	Lincoln Gamble	151.00
80	13:45:00	Only Alone	141.00
81	13:45:00	Castlehill Retreat	113.00
82	14:00:00	Chief Little Hawk	202.00
83	14:00:00	Eastern Voice	180.00
84	14:00:00	Hyde Park Barracks	153.00
85	14:00:00	Frenetic	236.00
86	14:00:00	All Things Bright	102.00
87	14:00:00	Blue Cabochon	172.00
88	14:00:00	Moon In Her Eye	148.00
89	14:00:00	Sweet Gardenia	116.00
90	14:00:00	Tilia Cordata	1.00
91	14:10:00	Hot Scoop	182.00
92	14:10:00	London Palladium	143.00
93	14:10:00	Sands Of Time	155.00
94	14:10:00	Bergerac	157.00
95	14:10:00	Gumdrop	117.00
96	14:10:00	Merry Secret	132.00
97	14:10:00	Cuban Breeze	185.00
98	14:20:00	Star In The Making	159.00
99	14:20:00	Oakenshield	147.00
100	14:20:00	Abstemious	152.00
101	14:20:00	Treble Treble	132.00
102	14:20:00	Breath Of Joy	185.00
103	14:20:00	Stone Soldier	147.00
104	14:20:00	Phuket Power	154.00
105	14:20:00	Many A Star	173.00
106	14:20:00	Hurcle	139.00
107	14:20:00	Melody King	153.00
108	14:20:00	Benefit Street	170.00
109	14:20:00	Custodian	118.00
110	14:20:00	Auckland Lodge	126.00
111	14:30:00	Aircraft Carrier	119.00
112	14:30:00	Broad Street	171.00
113	14:30:00	Giuseppe Garibaldi	153.00
114	14:30:00	Master Of Reality	223.00
115	14:30:00	Micro Manage	159.00
116	14:30:00	Sea The Lion	171.00
117	14:30:00	Maria Christina	140.00
118	14:30:00	Search For A Song	230.00
119	14:30:00	Delphi	264.00
120	14:30:00	Monument Valley	189.00
121	14:40:00	Spirit Of Sisra	146.00
122	14:40:00	Chase The Dollar	162.00
123	14:40:00	Calcutta Cup	154.00
124	14:40:00	Topper Bill	159.00
125	14:40:00	Scots Gold	106.00
126	14:50:00	Burmese Waltz	213.00
127	14:50:00	Natalies Joy	149.00
128	14:50:00	Shades Of Blue	242.00
129	14:50:00	Caspian Queen	153.00
130	14:50:00	Crispina	150.00
131	14:50:00	Exceptional	212.00
132	14:50:00	Jouska	179.00
133	14:50:00	Lambeth Walk	213.00
134	14:50:00	Magical Journey	192.00
135	14:50:00	Mountain Brave	184.00
136	14:50:00	Stormy Girl	176.00
137	15:00:00	Leo De Fury	228.00
138	15:00:00	Numerian	212.00
139	15:00:00	Rakan	193.00
140	15:00:00	Sinawann	267.00
141	15:00:00	Armory	247.00
142	15:00:00	Degraves	204.00
143	15:00:00	Helvic Dream	196.00
Top Rated

Cell Formulas
Range	Formula
I1:I14	I1	=UNIQUE(A1:A143)
J1:K1,J3:L3,J2,J4:J14	J1	=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]

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]

I have updated my details also.

Thanks for that.

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]

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
	A	B	C	D	E	F	G	H	I	J	K
1		12:15:00	Secret Handsheikh	149.00					12:15:00	Ballintoy Harbour	Ballintoy Harbour
2		12:15:00	Ancient Times	104.00					12:45:00	Favourite Niece	Favourite Niece
3		12:15:00	Able Love	1.00					13:00:00	Ballintoy Harbour	Acclamadic
4		12:15:00	Ballintoy Harbour	155.00
5		12:45:00	Carribean Queen	1.00
6		12:45:00	Favourite Niece	111.00
7		12:45:00	Three Hat Day	96.00
8		12:45:00	Waitingonanalibi	1.00
9		12:45:00	Zahrisa	108.00
10		13:00:00	Acclamadic	155.00
11		13:00:00	Baron Wild	108.00
12
Top Rated

Cell Formulas
Range	Formula
J1:J3	J1	=INDEX(C:C,MATCH(MAXIFS(D:D,B:B,I1),D:D,0))
K1:K3	K1	=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))

 

[Davebro]

=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))

Thanks again for your help, much appreciated.