Find last Combination and Return Date

Simon Wilcock

Hi

I want to be able to find a previous combination of an entry and return the date for that entry (not the created date, but the date in the date field)

I've been using the following to find the highest previous date but I want to integrate an extra criteria which is the last time a certain combination appeared.

=MAX(COLLECT(Date:Date, Date:Date, @cell < Date@row))

For example, when I add the last row for Manchester and Audit 1, I want the next cell to tell me when the last audit was completed, so in this case 05/07/2023. Ideally, also if there is no previous audit, enter None instead.

I've added a helper field to combine the Location and Type field into a single cell and then use that Helper column in the criteria but its not working.

Thanks

Kelly Moore

Hey @Simon Wilcock

Try this. If the system autonumber column isn't already in your sheet, you'll need that (Or, you can use the Created Date in it's place if you already have that. You would substitute [Row ID] with Created, in the formula below

=IFERROR(INDEX(Date:Date, MATCH(MAX(COLLECT(Date:Date, Date:Date, ISDATE(@cell), Location:Location, Location@row, Type:Type, Type@row, [Row ID]:[Row ID], @cell < [Row ID]@row)), Date:Date, 0)), "None")

Will this work for you?

Kelly