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

Find more posts tagged with

Accepted answers

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:

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.