Identifying Duplicates

Chris Warren

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

Bassam Khalil

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 Khalil

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:

Chris Warren

Thank you Bassam, that worked great. :)

Bassam Khalil

@Chris Warren

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