Is it possible to set up conditional formatting to alert when a duplicate entry is inputted?

Kristie Diersen

Is it possible to set up conditional formatting to highlight or alert someone if a duplicate entry is being inputted?

I am looking to highlight the DOC ID if it has previously been entered into the sheet.

Andrée Starå

Hi Kristie,

It’s not possible with a conditional formatting alone, but in combination with a formula it can be done.

Would that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Kristie Diersen

Andree,

How would this work with a formula? What kind of formula would be used for this?

Jayesh Nathani

Hi Kristie,

You will need to create a new column (I;m going to call it Duplicate), make it a checkbox type and apply this formula to it

=IF(ISBLANK([DOC ID]@row) = false, IF(COUNTIF([DOC ID]:[DOC ID], [DOC ID]@row) > 1, true), false)

Now for highlighting part:

Set a conditional formatting that - If Duplicate is checked, then apply this(your choice) format to the row.

Similarly for a alert - When Duplicate is checked send an alert.

Hope it helps!

Thanks,

Jayesh

Andrée Starå

@Kristie Diersen

Happy to help! 

I saw that Jayesh answered already!

Let me know if I can help with anything else!

Best, 

Andrée 

Kristie Diersen

That is perfect. Thank you!