Matching Highest to Name

[mlmrob]

Good Morning

I have a sheet where up to last five ratings of a racehorse are shown. I would like to take highest rating each horse has achieved and place that number next to the horses name further up the page.
The sheet is presented with today's racecards,Row 2 to Row 469.

From 470 to 2384 in Column I are the ratings that each horse achieved.

From Row 470 to 474 you can see the horse Adapt To Dan. In Row I are the ratings that horse achieved in its last five races.I would like to take the best rating in Row I and place it next to Adapt To Dan in Row 2. Any column will do.
Then the next horse, Astelia, row 475 to 478 and place its best rating from Row I next to its name in Row 3.And do on.

The numbers of horses can change daily so the range can be shorter or longer.

This is a link to the OneDrive account where it is sitting

formreport_2022-10-14.xlsx

Microsoft Excel Workbook

1drv.ms

Thank you

Rob

 

[etaf]

how about using MAXIFS
=MAXIFS(F:F,C:C,C2)

you dont say what version of excel you are using

I put in H but as i copied down - i found some data there

a snapshot of file here

formreport_2022-10-14.xlsx
	A	B	C	D	E	F	G	H
1	race_time	track	horse_name	furlongs	horse_weight	official_rating	DistBt
2	17:00:00	Dundalk	Adapt To Dan (IRE)	10.5	128	70		75
3	17:00:00	Dundalk	Astelia (IRE)	10.5	138	71		73
4	17:00:00	Dundalk	Boragh Empress (IRE)	10.5	123	50		59
5	17:00:00	Dundalk	Dalvey	10.5	138	83		91
6	17:00:00	Dundalk	Deluca Chop (FR)	10.5	124	65		71
7	17:00:00	Dundalk	Ghumama (IRE)	10.5	125	72		95
8	17:00:00	Dundalk	James Henry (IRE)	10.5	130	65		65
9	17:00:00	Dundalk	Movie King (IRE)	10.5	130	47		90
10	17:00:00	Dundalk	Never Back Down (IRE)	10.5	130	56		63
11	17:00:00	Dundalk	Power On (IRE)	10.5	135	0		60
12	17:00:00	Dundalk	Rene Artois (IRE)	10.5	128	77		82
13	17:00:00	Dundalk	Shore (USA)	10.5	132	53		64
14	17:00:00	Dundalk	Sir Lovealot (IRE)	10.5	123	0		0
15	17:00:00	Dundalk	Skontonovski	10.5	132	70		72
16	17:00:00	Dundalk	Supposedtobe (IRE)	10.5	132	70		70
17	17:00:00	Dundalk	Sweeping Statement (IRE)	10.5	133	71		77
18	17:00:00	Dundalk	Ten Coins (IRE)	10.5	126	62		70
19	17:30:00	Dundalk	A Shin Undine (IRE)	10.5	129	68		69
20	17:30:00	Dundalk	Action Motion (IRE)	10.5	133	65		72
21	17:30:00	Dundalk	Beer With The Boys	10.5	136	64		64
22	17:30:00	Dundalk	Cursory Exam	10.5	139	67		67
23	17:30:00	Dundalk	Darkened (IRE)	10.5	133	68		117
24	17:30:00	Dundalk	Draco Pulchrac (IRE)	10.5	141	69		69
25	17:30:00	Dundalk	Eastern Racer (IRE)	10.5	134	62		68
26	17:30:00	Dundalk	Innamorare (IRE)	10.5	132	60		66
27	17:30:00	Dundalk	King Arthurs Sword (IRE)	10.5	133	61		72
28	17:30:00	Dundalk	Lovesome Dove (IRE)	10.5	126	58		66
29	17:30:00	Dundalk	Mads Black Eights (IRE)	10.5	135	66		66
formreport_2022-10-14

Cell Formulas
Range	Formula
H2:H29	H2	=MAXIFS(F:F,C:C,C2)

 

[Georgiboy]

Depends on version, formula would need to be different in older versions of Excel:

formreport_2022-10-14.xlsx
	A	B	C	D	E	F	G	H	I	J
1	race_time	track	horse_name	furlongs	horse_weight	official_rating	DistBt
2	17:00:00	Dundalk	Adapt To Dan (IRE)	10.5	128	70				71.19375
3	17:00:00	Dundalk	Astelia (IRE)	10.5	138	71				65.125
4	17:00:00	Dundalk	Boragh Empress (IRE)	10.5	123	50				48.43182
5	17:00:00	Dundalk	Dalvey	10.5	138	83				85.24286
6	17:00:00	Dundalk	Deluca Chop (FR)	10.5	124	65				56.25
7	17:00:00	Dundalk	Ghumama (IRE)	10.5	125	72				60.21875
8	17:00:00	Dundalk	James Henry (IRE)	10.5	130	65				57.0625
9	17:00:00	Dundalk	Movie King (IRE)	10.5	130	47				86.53125
10	17:00:00	Dundalk	Never Back Down (IRE)	10.5	130	56				50.125
11	17:00:00	Dundalk	Power On (IRE)	10.5	135	0				57.39286
12	17:00:00	Dundalk	Rene Artois (IRE)	10.5	128	77				78.59375
13	17:00:00	Dundalk	Shore (USA)	10.5	132	53				57.21429
14	17:00:00	Dundalk	Sir Lovealot (IRE)	10.5	123	0				-27.9
15	17:00:00	Dundalk	Skontonovski	10.5	132	70				63.75
16	17:00:00	Dundalk	Supposedtobe (IRE)	10.5	132	70				63
17	17:00:00	Dundalk	Sweeping Statement (IRE)	10.5	133	71				72
18	17:00:00	Dundalk	Ten Coins (IRE)	10.5	126	62				54.53571
19	17:30:00	Dundalk	A Shin Undine (IRE)	10.5	129	68				66.78947
20	17:30:00	Dundalk	Action Motion (IRE)	10.5	133	65				65.0625
21	17:30:00	Dundalk	Beer With The Boys	10.5	136	64				59
22	17:30:00	Dundalk	Cursory Exam	10.5	139	67				60.875
formreport_2022-10-14

Cell Formulas
Range	Formula
J2:J22	J2	=MAXIFS(I:I,C:C,C2)

 

[mlmrob]

Thanks guys, I have Microsoft 365

 

[Fluff]

Thanks guys, I have Microsoft 365

In that case I suggest that you update your Account details (or 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’)

 

[mlmrob]

Worked perfectly, thank you.

 

[etaf]

you are welcome