Highlighting Duplicates in a Database?

Kevin H

Hello there, I am creating a customer management software with thousands of companies.

I'd like to highlight all duplicate entries under the "Company" column (See image below).

In an ideal world, both entries for the company "Effective Spend" would be highlighted since it is a duplicate.

Is anyone able to share how this might be possible?

Heather Duff

@Kevin H I may have found your answer:

Heather Duff

Hi @Kevin H ,

I would suggest that you create a "Duplicate" checkbox column with a column formula that looks like this:

=IF(COUNTIF(Company:Company, Company@row) > 1, 1, 0)

If you right click and set it as a column formula, it will automatically calculate the checked/unchecked status for new entries.

Then you can set up conditional formatting like this:

You can then hide the Duplicate column if you don't want it showing.

Hope this helps. Let me know if it works for you!

Best,

Heather

Andrée Starå

Hi @Kevin H

I hope you're well and safe!

Try something like this in a Checkbox or Flag type column. (drag it down from the first row)

=IF(COUNTIF(Company$1:Company@row) > 1; 1; 0) 

Another option (you can add this anywhere and make it a column formula)

=IF(COUNTIF(Company:Company;Company@row) > 1; 1; 0) 

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Would any of those options work/help?

I hope that helps!

Be safe and have a fantastic week!

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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Kevin H

Hi @Heather Duff -- When I right click, the option to make it a "column formula" is greyed out. In other words, I can't do it.. See below. Might this be because I have the free trial and need to upgrade?

If I can get that part solved, I think this will work wonders!

Your advice is greatly appreciated!

Heather Duff

@Kevin H I'm not sure if the free license restricts the use of column formula. Have you already entered the formula in that cell? It greys the convert to column formula option out if I right click on a cell that does not have a formula in it...

Andrée Starå

@Kevin H

Make sure that you click on the cell with the formula.

Did that work?

Heather Duff

@Kevin H I may have found your answer:

Kevin H

@Heather Duff That works! You're very helpful.

Heather Duff

@Kevin H Glad it worked for you! Have a great day.