Fill in Cells by obtaining data from a list of data, most recent data (date) first.

[James8761]

Hi,

I am a bit stuck on a project for Uni. (similar to yesterday).


The first bit of data below is the data I am looking up.

The data will always be sorted by horse, then most recent date first.



I then want to create 3 boxes to show the ‘Score’ a horse achieved.

I’ve inserted the Couse in Cell J1 and the Distance in Cell J2 to act as a lookup. The Horse name to lookup is in Cell I6.

The hope being that I can then change these for the thousands of rows I have on the whole dataset.



The first box in Cells I4:N6 I want to just look at the 5 most recent runs and split them in Cell J6, K6, L6, M6 and N6. I’ve manually input the answers for now.

The second box in Cells P4:T6 I want to look at the 5 most recent runs at the distance for the Horse (the Distance being 6f as stated in Cell J2). and split them in Cell P6, Q6, R6, S6 and T6. I’ve manually input the answers for now.

The third box in Cells V4:Z6 I want to look at the 5 most recent runs at the course and the distance for the Horse (Haydock is the course as stated in Cell J1 and 6f the Distance as stated in Cell J2). and split them in Cell V6, W6, X6, Y6 and Z6. I’ve manually input the answers for now.

Does anyone know if it is possible to create a formula rather than doing this manually please?



Thank you for any guidance.

 

[James8761]

It may be beacuse i'm on my works laptop and there is a security layer on it......I'll try on my own laptop tonight.....

Thank you very much for the formula......can I just ask it wouldn't let me use the FILTER function.......just checked my version of Excel and it is now saying Office 365 ProPlus.......is that why the FILTER function won't appear.........it was O365 the last time I checked (admittedly a few months ago!)

 

[Fluff]

Yup, that's the reason, you wont get the dynamic array functions until (I think) July.
I'll be back shortly.

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E			H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Date	Horse	Score	Distance	Course					Haydock
2		Hopeless	55	6f	Haydock					6f
3		Hopeless	47	6f	Lingfield
4		Hopeless	49	7f	Lingfield
5		Hopeless	34	5f	Kempton					Last 5						Last 5 distance						Last distance & course
6		Hopeless	35	5f	Bath				Hopeless	55	47	49	34	35		55	47	59	20	58		55	20	64	22	43
7		Hopeless	59	6f	Bath
8		Hopeless	20	6f	Haydock
9		Hopeless	58	6f	Leicester
10		Hopeless	61	6f	Kempton
11		Hopeless	56	6f	Lingfield
12		Hopeless	22	5f	Bath
13		Hopeless	41	5f	Kempton
14		Hopeless	63	5f	Kempton
15		Hopeless	60	5f	Leicester
16		Hopeless	39	6f	Leicester
17		Hopeless	69	6f	Bath
18		Hopeless	64	6f	Haydock
19		Hopeless	22	6f	Brighton
20		Hopeless	68	5f	Brighton
21		Hopeless	38	5f	Brighton
22		Hopeless	46	5f	Brighton
23		Hopeless	59	5f	Brighton
24		Hopeless	22	6f	Haydock
25		Hopeless	50	5f	Lingfield
26		Hopeless	43	6f	Haydock
27		Glory	35	6f	Haydock
28		Glory	51	6f	Lingfield
29		Glory	53	7f	Lingfield
30		Glory	33	5f	Kempton
31		Glory	34	5f	Bath
32		Glory	62	6f	Bath
Master

Cell Formulas
Range	Formula
J6:N6	J6	=INDEX($C$2:$C$40,AGGREGATE(15,6,(ROW($C$2:$C$40)-ROW($C$2)+1)/($B$2:$B$40=$I$6), COLUMNS($J6:J6)))
P6:T6	P6	=INDEX($C$2:$C$40,AGGREGATE(15,6,(ROW($C$2:$C$40)-ROW($C$2)+1)/(($B$2:$B$40=$I$6)*($D$2:$D$40=$J$2)), COLUMNS($P6:P6)))
V6:Z6	V6	=INDEX($C$2:$C$40,AGGREGATE(15,6,(ROW($C$2:$C$40)-ROW($C$2)+1)/(($B$2:$B$40=$I$6)*($D$2:$D$40=$J$2)*($E$2:$E$40=$J$1)), COLUMNS($V6:V6)))

 

[James8761]

Excellent......thanks so much for your time......the formula(s) do exactly as I want..

 

[Fluff]

You're welcome & thanks for the feedback.