Finding Duplicate entries across three columns

Options

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???

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!