Formula - Helper Column Flag if Duplicate

Estephania

Is there a formula I can use in a helper column to flag an entry if a duplicate already exist? We are going to share add forms with 100+ people to enter information about their employees. The last time we did this many of them entered duplicate employees causing a lot of chaos.

The goal is to send out alerts/notifications if the helper column detects a duplicate.

Andrée Starå

Hi @Estephania

I hope you're well and safe!

Here's an example of how it can be structured. This will show all duplicates (the original and the duplicate(s)).

Add the formula to a so-called helper column (Checkbox), and update the column name to match yours.

=IF(ColumnName@row <> "", IF(COUNTIF(ColumnName:ColumnName, ColumnName@row) > 1, 1))

Would that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Estephania

Hi @Andrée,

Thank you for your response. I tried this formula as shown above and am having trouble making it work. See screenshot. I am not sure what I'm doing wrong.

I have tried changing a few things, but it either doesn't bring any results, or I get errors.

Eric Law

@Estephania Here is an easier formula for you, =IF(COUNTIF([Employee ID]:[Employee ID],[Employee ID]@row)>1,1,0)

Or you can throw in an iferror too =IFERROR( IF(COUNTIF([Employee ID]:[Employee ID],[Employee ID]@row)>1,1,0),0)

DKazatsky2

Hi @Estephania,

The original formula provided by @Andrée Starå works with the addition of a "=" in the COUNTIF condition.

=IF([Employee ID]@row <> "", IF(COUNTIF([Employee ID]:[Employee ID], =[Employee ID]@row) > 1, 1))

FYI...the "=" is also needed if you use the formula provided by @Eric Law (at least in my testing).

=IF(COUNTIF([Employee ID]:[Employee ID], =[Employee ID]@row) > 1, 1, 0)

Hope this helps,

Dave

Estephania

Thank you @Eric Law and @Andrée,

I found the reason it was not working from the beginning was that it did not like the numbers in the Employee ID column starting with zero (0). Once I put a letter in front of the number the formulas worked!

Thank you both!