Formula to find most recent duplicate

Zach_

Hi there!

I'm working on an inventory of documents. The formula I want is one that would enable me to determine the last time a resource was updated. It'll use a checkbox column.

Below is a list of what I need the formula to do. I think the bolded step is where I'm having the most trouble.

1. Check whether the "Resource" value for the current item is identical to the "Resource" value for any other items. (This is because each resource ends up getting updated multiple times, so we log each update.)
2. Compare the "Date Marked Completed" value for the current item to the "Date Marked Completed" value for any items identified in step 1 (This is so, if a resource was updated multiple times, I can determine which log represents the most recent update.)

If an item has a unique "Resource" value, the checkbox should display true. If an item does not have a unique "Resource" value but has a "Date Marked Completed" value which is greater than that of the other items with an identical "Resource" value, display true. If an item does not have a unique "Resource" value and does not have a "Date Marked Completed" value which is greater than that of the other items with an identical "Resource" value, display false.

Here is a screenshot showing the relevant columns.

Thank you for the help! I'm stumped on this one, and the AI formula writer isn't helping.

Heidi Truitt

=IF(NOT(ISDATE([Date Marked Completed]@row)),FALSE,IF(COUNTIF([Resource]:[Resource], Resource]@row) =1,TRUE,IF([Date Marked Completed]@row = MAX(COLLECT([Date Marked Completed]:[Date Marked Completed],
[Resource]:[Resource], [Resource]@row,[Date Marked Completed]:[Date Marked Completed], ISDATE(@cell))),TRUE,FALSE)))

This one might also get us there

Heidi Truitt

=IF(COUNTIF([Resource]:[Resource], [Resource]@row) = 1,TRUE,IF([Date Marked Completed]@row =MAX(COLLECT([Date Marked Completed]:[Date Marked Completed], [Resource]:[Resource], Resource]@row)),TRUE,FALSE))

Zach_

@Heidi Truitt, thank you! This gets me way closer to where I want to be.

I'm noticing that it's still not quite right, though. For documents that appear in the list exactly once, the checkbox displays true, even if there is no "Date Marked Completed" value (i.e., the planned update is either pending or in progress, not complete). I was probably unclear about this — the presence of a "Date Marked Completed" value should take precedence over an item being unique. Is this possible? This is one of the parts where I got hung up.

Heidi Truitt

=IF(COUNTIF([Resource]:[Resource], [Resource]@row) = 1,TRUE,IF(AND(ISDATE([Date Marked Completed]@row),
[Date Marked Completed]@row = MAX(COLLECT([Date Marked Completed]:[Date Marked Completed],[Resource]:[Resource], [Resource]@row,[Date Marked Completed]:[Date Marked Completed], ISDATE(@cell)))),TRUE,FALSE))

Try this and see if it will work 😊

Zach_

Hi again, @Heidi Truitt. No, that didn't change anything.

Heidi Truitt

=IF(NOT(ISDATE([Date Marked Completed]@row)),FALSE,IF(COUNTIF([Resource]:[Resource], Resource]@row) =1,TRUE,IF([Date Marked Completed]@row = MAX(COLLECT([Date Marked Completed]:[Date Marked Completed],
[Resource]:[Resource], [Resource]@row,[Date Marked Completed]:[Date Marked Completed], ISDATE(@cell))),TRUE,FALSE)))

This one might also get us there

Zach_

@Heidi Truitt, thank you very, very much. It appears to be working exactly as I hoped it would. I greatly appreciate your help.

Heidi Truitt

@zach_ I am so glad!