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

 

[jasonb75]

Try this in C1 and drag right. Formula is set for 100 rows of data in the Data sheet, you can adjust as needed.

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

 

[James8761]

Try this in C1 and drag right. Formula is set for 100 rows of data in the Data sheet, you can adjust as needed.

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

Hi, thanks for the reply. It does enter a number, but it inputs 0, in each cell.

Looking manually, C1 should be 8 and D1 should be 9. Then E1, F1, and g1 RETURNS #NUM!. The last bit is an error as this person has only run twice at Exeter, so I get that and I jst put an IFERROR at the front of the formula and that sprts that out, Just struggling to see why it returns 0 and 0 as opposed 8 and 9.

Kind regards,
James

 

[jasonb75]

That's my fault, I had a major brainfart
I'll be back with a less flatulent formula shortly...

edit:- think this is right

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

 

[James8761]

That's my fault, I had a major brainfart
I'll be back with a less flatulent formula shortly...

edit:- think this is right

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

Thanks Jason, I have input that, but every formula result is now blank cells.

 

[jasonb75]

Dates in text format? Merged cells?

The formula should work if everything is as you described.

Try, =AGGREGATE(15,6,Data!$A$2:$A$100/(Data!$C$2:$C$100=$A1)/(Data!$D$2:$D$100=$B1),COLUMNS($C1:C1))

Does it return a 5 digit number or a #NUM error?

If it gives you a number then see if this one returns a number or an error.
=MATCH(AGGREGATE(15,6,Data!$A$2:$A$100/(Data!$C$2:$C$100=$A1)/(Data!$D$2:$D$100=$B1),COLUMNS($C1:C1)),$A$2:$A$100,0)

 

[James8761]

Dates in text format? Merged cells?

The formula should work if everything is as you described.

Try, =AGGREGATE(15,6,Data!$A$2:$A$100/(Data!$C$2:$C$100=$A1)/(Data!$D$2:$D$100=$B1),COLUMNS($C1:C1))

Does it return a 5 digit number or a #NUM error?

If it gives you a number then see if this one returns a number or an error.
=MATCH(AGGREGATE(15,6,Data!$A$2:$A$100/(Data!$C$2:$C$100=$A1)/(Data!$D$2:$D$100=$B1),COLUMNS($C1:C1)),$A$2:$A$100,0)

Thanks for the response Jason.....

Try, =AGGREGATE(15,6,Data!$A$2:$A$100/(Data!$C$2:$C$100=$A1)/(Data!$D$2:$D$100=$B1),COLUMNS($C1:C1))

This one gives me a 5 digit number 43818

=MATCH(AGGREGATE(15,6,Data!$A$2:$A$100/(Data!$C$2:$C$100=$A1)/(Data!$D$2:$D$100=$B1),COLUMNS($C1:C1)),$A$2:$A$100,0)

The above one gives me an #N/A error

I have checked the date format on Column A of the data sheet and it is set up as Date *14/03/2012

Thanks again.

 

[jasonb75]

That means that the dates are formatted as text. If they start off as text then applying a date format in the number format box doesn't fix them (things would be so much easier if it did).

Select column A in the data sheet, go to the Data tab on the excel ribbon, then Text To Columns.
Click Next once, then uncheck all boxes.
Click Next again, choose the correct date format from the dropdown box (DMY if 09/03/2020 is 9th March or MDY if it is September 3rd), then click finish.

Now the formula in post 4 should work (as long as there are no other anomalies).

 

[James8761]

Thanks for all your help Jason. There must be something else wrong with the data as it still returns blank cells.

I'll see if there's anyway else they can send it to me.

Thanks again,
James

 

[jasonb75]

Does the second formula from post 6 still return #N/A? If it does then that would suggest that text to columns wasn't applied correctly.
Try formatting column A as short date from the formatting options before applying text to columns.
One other thought, are there merged cells in the sheet? They almost always cause problems.