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.

 

[Fluff]

A couple of things.
1) Can you please post your data using the XL2BB add-in, it saves members from having to create it themselves.
2) What version of Excel are you using? Please update your account details to show this.

 

[James8761]

Right, so I've done the second part


I have followed the instructions......for 1) above but do not have XL2BB in Excel after downloading it.

 

[Fluff]

Did you copy the xlam file to your add-ins folder?

 

[James8761]

I've got that bit...but I haven't got any MrExcel ribbon

 

[Fluff]

That's odd, is the add-in listed in the Add-ins list?
Also are you using the desktop version of Xl or the Web based version?

 

[James8761]

I've got the desktop version.....below is where it shows the add on....

 

[Fluff]

If you goto the add-ins folder & right click the XL2BB.xlam file then select unblock.

 

[James8761]

Thanks for the help but when I right click nothing happens. Thanks for trying though.

 

[Fluff]

Ok, I've typed it out. How about

+Fluff New.xlsm
	A	B	C	D	E			H	I	J	K	L	M	N
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
6		Hopeless	35	5f	Bath				Hopeless	55	47	49	34	35
7		Hopeless	59	6f	Bath					Last 5 distance
8		Hopeless	20	6f	Haydock					55	47	59	20	58
9		Hopeless	58	6f	Leicester					Last distance & course
10		Hopeless	61	6f	Kempton					55	20	64	22	43
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(FILTER(C2:C40,B2:B40=I6),{1,2,3,4,5})
J8:N8	J8	=INDEX(FILTER(C2:C40,(D2:D40=J2)*(B2:B40=I6)),{1,2,3,4,5})
J10:N10	J10	=INDEX(FILTER(C2:C40,(D2:D40=J2)*(E2:E40=J1)*(B2:B40=I6)),{1,2,3,4,5})
Dynamic array formulas.