Need a formula to search for the most recent value in Column B for similar values in Column A

Tony Di Paolo

Hello,

Would someone know how to build a formula for the following situation?

We have a sheet with employee info with a name column (say Column A) and a status column (say Column B).

When we make a change to an employee, we create a new row. In the Status column, we select the change from a drop-down.

I need to have a checkmark column that identifies the row with the latest change in Column B for each employee (an employee may have 1 to multiple rows).

Thank you for your help.

Tony

Nick Korna

Hi @Tony Di Paolo ,

If you're not altering any of the previously entered statuses, then this should work for you:

Add in a column C as system generated modified date and column D for your checkbox.

The following formula in D will tick the latest dated entry for each employee:

=IF(MAX(COLLECT(C:C, A:A, A@row)) = C@row, 1, 0)

Example (using manually entered rather than system generated modified dates):

You can then use a filter to find only the latest (i.e. ticked) ones.

If I've misunderstood something, let me know and I'll try and fix it for you, but I hope this helps!