Logic: Looking for the Duplicate Function and Conditional Logic

NCNWIncData · 06/01/25

Good Evening,

Essentially, I want to search for the duplicate in both lists and then:

Option #1: delete the duplicate from the Comms-LIST Deliverable

Option #2: if deletion is not possible then white out the text in the Comm-List Deliverable

Thanks as always for your brilliant mind,

NCNW, Inc.

KPH · 06/01/25

You can achieve either outcome by adding a column to the sheet to identify the rows to delete or format and using the value in that to trigger the deletion or formatting.

I'll use this list to illustrate:

Step 1 Identify the duplicate rows

You can use a COUNTIFS function to count the number of times the value in the Comms-Deliverable list is in the Comms-LIST-Deliverable.

In my example, the formula for that would be

=COUNTIFS([List 2]:[List 2], [List 1]@row)

(Your column names will be different)

Step 2 Exclude blank rows

You don't want to be deleting rows that are blank in list 1 just because of lots of blank rows in list 2, so can add an IF and ISBLANK to the formula to count 0 for any blank rows.

=IF(ISBLANK([List 1]@row), 0, COUNTIFS([List 2]:[List 2], [List 1]@row))

Step 3 Return the trigger

Now, you can add another IF to return a number, text symbol, etc, if the count of duplicate rows is greater than zero. In my example, I return the word "Delete".

=IF(IF(ISBLANK([List 1]@row), 0, COUNTIFS([List 2]:[List 2], [List 1]@row)) > 0, "Delete")

Make this a column formula.

Step 4 Delete or Format

4a Delete

Create an automation where the trigger is where a row is added or changed and the value in the column you created to identify duplicates (steps 1-3) contains the keyword you specified in step 3 (in my case the word "delete").

Set the action to clear the value in your first list column.

This will just clear list 1. You can also clear other cells, if needed.

Note: This will only run when a row is added or changed. If you already have your data in the sheet, and the duplicate finder column populated, then add the automation, only rows that are subsequently tagged as duplicates, will be cleared. To trigger existing rows, copy the formula and then delete it so the Duplicate Finder column is blank, save the sheet, paste the formula back in, save again.

4b Format

You can use Conditional Formatting and set a condition so that when your column contains the keyword you specified in step 3 (in my case the word "delete") format the enter row to white text.