Return most recent date of co-located sites

Kevin7859

I am running a yearly list of sites that are being inspected and need to return the most recent inspection date for those sites that are co-located (same address, different site IDs)

I have tried using the index countifs and get Incorrect data types even though all the date columns and the co-located visit helper column are set to date type.

MarceHolzhauzen

Hi @Kevin7859

Have you tried using MAX(COLLECT())?

It will look something like:
=MAX(COLLECT([23-24SY Date Visit]:[23-24SY Date Visit], Co-Located@row, 1, [Co-Located With]@row, [Site ID]@row))

Explanation:

1. COLLECT([23-24SY Date Visit]:[23-24SY Date Visit], Co-Located@row, 1, [Co-Located With]@row, Site ID@row):
   • [23-24SY Date Visit]:[23-24SY Date Visit]: This specifies the range from which you're collecting the data (dates).
   • Co-Located@row, 1: Filters the data for rows where the Co-Located value equals 1.
   • [Co-Located With]@row, Site ID@row: Adds another filter where the Co-Located With value matches the Site ID.
2. MAX(...):
   • The MAX function will return the latest date from the results of the COLLECT function.

This formula retrieves the maximum (latest) date from the [23-24SY Date Visit] column where both the Co-Located and Co-Located With conditions are met.

Hope this helps

Kevin7859

I have tried the formula but it is returning an error of #Incorrect Argument Set.

Part of the issue is that the "Co-Located With" is not a direct @row match and some of them return more than 1 item in the cell.