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

James8761

Board Regular
Joined
Apr 24, 2012
Messages
139
Office Version
  1. 2019
Platform
  1. Windows
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.

1588594400356.png


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.

1588594481128.png



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.

1588594502952.png


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.
1588594527561.png


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.

1588594547139.png




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



Thank you for any guidance.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,423
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 24, 2012
Messages
139
Office Version
  1. 2019
Platform
  1. Windows
1588598712682.png



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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,423
Office Version
  1. 365
Platform
  1. Windows
Did you copy the xlam file to your add-ins folder?
 

James8761

Board Regular
Joined
Apr 24, 2012
Messages
139
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,423
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 24, 2012
Messages
139
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

1588600460538.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,423
Office Version
  1. 365
Platform
  1. Windows
If you goto the add-ins folder & right click the XL2BB.xlam file then select unblock.
 

James8761

Board Regular
Joined
Apr 24, 2012
Messages
139
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the help but when I right click nothing happens. Thanks for trying though.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,423
Office Version
  1. 365
Platform
  1. Windows
Ok, I've typed it out. How about

+Fluff New.xlsm
ABCDEHIJKLMN
1DateHorseScoreDistanceCourseHaydock
2Hopeless556fHaydock6f
3Hopeless476fLingfield
4Hopeless497fLingfield
5Hopeless345fKemptonLast 5
6Hopeless355fBathHopeless5547493435
7Hopeless596fBathLast 5 distance
8Hopeless206fHaydock5547592058
9Hopeless586fLeicesterLast distance & course
10Hopeless616fKempton5520642243
11Hopeless566fLingfield
12Hopeless225fBath
13Hopeless415fKempton
14Hopeless635fKempton
15Hopeless605fLeicester
16Hopeless396fLeicester
17Hopeless696fBath
18Hopeless646fHaydock
19Hopeless226fBrighton
20Hopeless685fBrighton
21Hopeless385fBrighton
22Hopeless465fBrighton
23Hopeless595fBrighton
24Hopeless226fHaydock
25Hopeless505fLingfield
26Hopeless436fHaydock
27Glory356fHaydock
28Glory516fLingfield
29Glory537fLingfield
30Glory335fKempton
31Glory345fBath
32Glory626fBath
Master
Cell Formulas
RangeFormula
J6:N6J6=INDEX(FILTER(C2:C40,B2:B40=I6),{1,2,3,4,5})
J8:N8J8=INDEX(FILTER(C2:C40,(D2:D40=J2)*(B2:B40=I6)),{1,2,3,4,5})
J10:N10J10=INDEX(FILTER(C2:C40,(D2:D40=J2)*(E2:E40=J1)*(B2:B40=I6)),{1,2,3,4,5})
Dynamic array formulas.
 

Forum statistics

Threads
1,137,347
Messages
5,680,959
Members
419,946
Latest member
Trickay

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