Formula for last recorded date

[bongofbobledore]

Hey friends!

I have an issue finding a formula that helps enter the last recorded date into a column.

Attached is an example of the sheet I am focusing on. The table headers that are in blue represents a PivotTable that I have created to filter the data from a larger pool of data.

The cells highlighted in yellow are where I want the latest date recorded to be entered.

Please refer to sheet 2 in the example book that I have provided. Sheet 2 is when I use the PivotTable to show the dates of the person that claimed a gift. I would like to transfer the last date (highlighted in yellow) to the cells highlighted in yellow.

Thank you for reading and helping! I know the way I explain might be confusing so just post a comment on the thread and I will reply asap. Once again, thank you so much!

[FDibbins]

Hi, welcome to the forum

It will be hard to ID the dates you want, because of the way you have the data laid out - you need to have the Label in 1 column, and the date in another column
Also, your dates are not really dates (to excel), they are text looking like a date and we will need to convert them

Would you be OK with adding a helper to put the labels in each row?

If so, use this in L4
L4=IF(ISERROR(DATE(RIGHT(A4,4),MID(A4,4,2),LEFT(A4,2))),A4,L3)
copied down.
To convert the date...
M4=IFERROR(DATE(RIGHT(A4,4),MID(A4,4,2),LEFT(A4,2)),"")
copied down

Then to get the latest date for each label...
G4=MAX(IF(Sheet2!$L$4:$L$25=Sheet1!A4,Sheet2!$M$4:$M$25))
ARRAY entered ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[kvsrinivasamurthy]

ARRAY formula in G4 of Sheet2 then copied down.

=IF(ISERROR(1*$A4),INDEX($A5:$A$25,IFERROR(MATCH(1,--ISERROR(1*$A5:$A$25),0)-1,ROW($A$25)-ROW())),"")

In G4 of Sheet1 then copied down.

=IFERROR(AGGREGATE(15,6,(Sheet2!$G$4:$G$25)/(Sheet2!$A$4:$A$25=Sheet1!$A4),1),"")

How ARRAY formula is entered

Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.

[bongofbobledore]

FDibbins:

Thank you very much. It works for the example file I have created. I will be trying to apply this to the rest of the data I have.

Brian

[bongofbobledore]

kvsrinivasamurthy:

Thank you!