Identifying Duplicates

Options

Hi 😀

I am having trouble working out a solution to a problem. I am identifying duplicates in a list of invoices as per the below using a COUNTIFS formula.

The problem i have is when an invoice number starts with a zero it has an apostrophe at the start of the number and this throws the formula off which produces a false positive for a duplicate.

The formula is - =IFERROR(IF(LEN(Invoice@row) = 0, "", IF(COUNTIFS(Invoice:Invoice, Invoice@row, Date:Date, Date@row, Debit:Debit, Debit@row, Credit:Credit, Credit@row) = 1, 0, 1)), 0)

Screen shots below.

The only thing i can thing to do is have a helper column that will add a Letter prefix to all the invoice numbers and this would hopefully get rid of the problem but i dont think this solution is idea.

Any help is much appreciated.

Thank you


Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Chris Warren

    Hope you are fine, please try the following formula and convert it to column format formula:

    =IFERROR(IF(COUNTIFS(Invoice:Invoice, @cell = Invoice@row, Date:Date, @cell = Date@row, Debit:Debit, @cell = Debit@row, Credit:Credit, @cell = Credit@row) > 1, 1), "")

    the following screenshot shows the result:


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Chris Warren

    Hope you are fine, please try the following formula and convert it to column format formula:

    =IFERROR(IF(COUNTIFS(Invoice:Invoice, @cell = Invoice@row, Date:Date, @cell = Date@row, Debit:Debit, @cell = Debit@row, Credit:Credit, @cell = Credit@row) > 1, 1), "")

    the following screenshot shows the result:


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Chris Warren
    Chris Warren ✭✭
    Options

    Thank you Bassam, that worked great. :)

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Chris Warren

    Excellent, i will be happy to help you any time.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!