Populating Cells based on most recent date occurrence

[James8761]

Hi,


Not even sure if this is possible, I have googled but couldn’t find how to do it.


I have a very large database/spreadsheet. I am trying to look at runners times at a given location and show me their finishing position, showing me their 5 most recent finishing positions.


In Sheet 2, I have

A1: A Smith (Runners Name)

B1: Exeter (Location)

In Cell C1 I would like to put the most recent finishing position for A Smith at Exeter. This would have been on 09/03/2020

In Cell D1 I would like to put the second most recent finishing position for A Smith at Exeter. This would have been on 09/01/2020

In Cell E1 I would like to put the third most recent finishing position for A Smith at Exeter. This would have been on 09/12/2019

In Cell F1 I would like to put the fourth most recent finishing position for A Smith at Exeter. This would have been on 09/10/2019

In Cell G1 I would like to put the fifth most recent finishing position for A Smith at Exeter. This would have been on 11/09/2019


In my database/spreadsheet (tab called Data). I have the information in the following Columns.


Runners Name in Column C

Location in Column D

Date in Column A

Finishing Position in Column E


Any help greatly appreciated to save me a lot of manual work.



Kind regards,

James

 

[James8761]

Hi Jason Thanks, I honestly don't know what else to try on that. I can't share that info due to GDPR. So I am trying it on some horse results data I have got, this is a Uni project I have chosen. Trying to do the exact same thing. This is the Data sheet below.

On my Sheet1. I have the Horse name in Cell A1, and the Course in Cell B1. In Cell C1 I would like to have Late Shipment's most recent finishing position at Hereford. So the answer would be 1, then the second most recent result in Cell D1, so this would be 2.

The end spreadsheet should like this: (biuut I can't get it to work). There are no merged cells anywhere.

Name	Course	FP1	FP2	FP3	FP4	FP5
Late Shipment	Hereford	1	2	8	6	5

Just incase you are bored this Sunday afternoon!

Thanks for any guidance.

 

[jasonb75]

I just realised that the formula would not work because of dates most likely being repeated in column A, although it still doesn't fully explain why it wasn't working, it should have still given a result, just not the right one.

Anyway, taking a different approach to try and rectify both problems, see if this one works. In order to simplify it, I've assumed that nobody can run the same location twice in one day.

=IFERROR(INDEX(Data!$E:$E,AGGREGATE(15,6,ROW(Data!$A$2:$A$100)/(Data!$A$2:$A$100=AGGREGATE(15,6,Data!$A$2:$A$100/(Data!$C$2:$C$100=$A1)/(Data!$D$2:$D$100=$B1),COLUMNS($C1:C1))),1)),"")

It's a bit of a messy formula, hopefully I've got all the parentheses in the right places. Note that when you edit the formula to fit your real data, all of the ranges in bold above must start and end on the same rows.

 

[James8761]

Thanks very much Jason.....almost there........just one problem....I've changed the 100 to 1000 to get the required data. The only problem now seems to be the date issue. So two horses run on the same date at Hereford. On the 10/03/2018 and 15/12/2018 Definately Vinnie and Late Shipment ran in the same race. This is the data filtered fro Hereford.....

The above formula gives me the answer:

i.e. it's picking up the result for Definately Vinnie in the first two (3 and PU) whereas I'm trying to look for Late Shipment last 5 results which would then be 5,6,8,2,1

Thanks very much for your persistence.

 

[jasonb75]

Yep, as soon as I read your reply I realised my mistake, I forgot to fix the date problem that I mentioned earlier. This one should work (I hope)

=IFERROR(INDEX(Data!$E:$E,AGGREGATE(15,6,ROW(Data!$A$2:$A$1000)/(Data!$C$2:$C$1000=$A1)/(Data!$D$2:$D$1000=$B1)/(Data!$A$2:$A$1000=AGGREGATE(15,6,Data!$A$2:$A$1000/(Data!$C$2:$C$1000=$A1)/(Data!$D$2:$D$1000=$B1),COLUMNS($C1:C1))),1)),"")

 

[James8761]

Yep, as soon as I read your reply I realised my mistake, I forgot to fix the date problem that I mentioned earlier. This one should work (I hope)

=IFERROR(INDEX(Data!$E:$E,AGGREGATE(15,6,ROW(Data!$A$2:$A$1000)/(Data!$C$2:$C$1000=$A1)/(Data!$D$2:$D$1000=$B1)/(Data!$A$2:$A$1000=AGGREGATE(15,6,Data!$A$2:$A$1000/(Data!$C$2:$C$1000=$A1)/(Data!$D$2:$D$1000=$B1),COLUMNS($C1:C1))),1)),"")

That's absolutely excellent.......thank you so much for your time and patience.......does exactly what I want it to.
Have a good remainder of the evening.

Thanks again,
James