‘VLOOKUP’ Formula To Calculate Latest Date Associated To ‘Parent’ Record

Hi,

can anyone please advise me of the ‘VLOOKUP’ formula I require to find the latest date for each ‘Parent’ record.

Basically, I have a worksheet with data in the ‘Parent’ record from columns ‘A’ to ‘AU’, however, in column ‘T’ (Note) which is text and column ‘U’ (Note Creation Date) which is a date format (dd-mm-yyyy) where it contains a list of notes associated to the parent record and the date it was created.

In the attached file (Example data.xlsx' the ‘Parent’ records are in rows 2. 61. 68 and 86 and can numerous ‘Child’ records associated i.e. zero entries, 1 enrty, or numerous entries

What I require (I think) is a ‘VLOOKUP ‘formula to calculate the latest date for the note and the note itself for each ‘Parent’ record and enter this data on the ‘Parent’ row in column ‘V’ (Latest Note) and Column ‘W’ (Latest Date)

Any assistance would be greatly appreciated

Many thanks in advance

Regards

Rob


N.B. Excel Version 2010

Pete, the 'Parent' row will always be the row with data in columns 'A' to 'U' with the subsequent associated 'Child' records only having data in the columns 'T' and 'U'.

The 'ID' in column 'A' is a unique number, so the rows of 'Child' records will be directly underneath the 'Parent' record until the next 'ID' is reached in column 'A'

Hope this helps


Regards


Rob

Hi all,

I’ve managed a workaround for my problem (see attached workbook ‘Example Data Version 2 on the ‘Updated’ tab) , whereby I have copied all the data down for each ‘Parent’ to the associated ‘Child’ rows record i.e. columns ‘A’ to ‘S’ and then used a formula in column ‘V’ (Helper) to calculate the newest record (using MAX) with formulas in column ‘W’ (latest Note) and column ‘X’ (Latest Note). What I will have to do then is to filter on column ‘V’ for ‘Newest’ and Bingo I have the data I need.

My problem now is I have to manually copy the data for each ‘Child’ record down for each ‘Parent’ record in order for the formula to work.

Can anyone help me please with a macro/VNA code required to copy the data down for each ‘Child’ record i.e. columns ‘A’ to ‘S’

Any assistance in this matter would be greatly appreciated

Regards

Rob