Finding Duplicate entries across three columns

Tara Newman

I am working on our departments budget worksheet that is linked in with our Finance department. I have found that entries are being duplicated and can not figure out why. They have the same time stamp and information. There are 2 columns that I am cross referencing and sometimes 3 to find these duplications. I have tried several of the conditional formatting rules as well as the check box and I can not get this to work.

I am looking for “ If the Vendor Name and Amount are the same ( and if there is an invoice number and it is the same) the marked so I can then move it to another sheet.

Any Advice???

Genevieve P.

Hi @Tara Newman

The way to do this is to first use a COUNTIFS formula to count how many rows match that same criteria, and then if this COUNTIF is more than 1, check a box.

COUNTIFS portion:

COUNTIFS([Vendor Name]:[Vendor Name], [Vendor Name]@row, Amount:Amount, Amount@row, [Invoice Number]:[Invoice Number], [Invoice Number]@row)

Embedded in an IF:

=IF(COUNTIFS([Vendor Name]:[Vendor Name], [Vendor Name]@row, Amount:Amount, Amount@row, [Invoice Number]:[Invoice Number], [Invoice Number]@row) >1, 1, 0)

Then put this in a checkbox column. You can use this helper checkbox column in your Conditional formatting if you'd like, too!

Let me know if this works for you.

Cheers!

Genevieve