# Identifying Duplicates

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

• ✭✭✭✭✭✭

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:

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"

• ✭✭✭✭✭✭

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:

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"

• Thank you Bassam, that worked great. :)

• ✭✭✭✭✭✭

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

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!