Displaying Most Current Info

LaBeach

To Whom it May Concern:

Goal: Automate filtering data based on two column values. I want to display the most current record of a duplicate entry in a report/dashboard.

Problem: I do not know the best approach. I have setup a sheet to collect 7 pieces of information: Duplicate PPR, Employee ID (PPR), Last Name, First Name, Full Name, Current, and Submission Date. I have setup the Duplicate PPR column as formula controlled column. The applicable cell will trigger once it notices that there are duplicate entries in the Employee ID column; the Employee ID column is my primary column.

Duplicate PPR Column Formula: =IF(ISBLANK([Employee ID (PPR)]@row), 0, IF(COUNTIF([Employee ID (PPR)]:[Employee ID (PPR)], [Employee ID (PPR)]@row) > 1, 1))

What I would like to happen next is, using the current column, have a formula setup similarly to the Duplicate PPR Column to trigger when a new record entry is submitted but is a duplicate to one that already exists. The formula should use the duplicate column, Employee ID, and submission date to check the box in the current column. The formula will find any checked checkboxes for Dup. PPR and check the box for any record that has the most current date. Similar to what is shown below:

I have come with the following formula but I have not gotten it to work yet:

Current Column Formula: =IF(AND([Duplicate PPR]@row = 1, [Submission Date]@row > TODAY([Submission Date]@row)), 1)

I get the following from the cell:

Any help will be greatly appreciated!

Best Regards,

LaBeach

Ryan Sides

Hi @LaBeach , if what you're considering as a "duplicate" is the person's Full Name, you can use the following formula which includes both MAX and COLLECT to gather the latest Submission Date and compare it to the current row. If it finds the latest is the same row, it checks the box. Otherwise, it leaves the box blank.

This formula also accounts for different names in the Full Name column as shown below.

=IF(MAX(COLLECT([Submission Date]:[Submission Date], [Full Name]:[Full Name], [Full Name]@row)) = [Submission Date]@row, 1, 0)