Duplicate Checker based on multiple conditions

Hello, I have a duplicate checker formula that was working perfectly until recently..

The recent change was that we started getting phone numbers in different formats (see screenshot).

Here's the formula that has been working perfectly: =IFERROR(IF(LEN([Person - Phone]@row) = 0, "", IF(COUNTIFS([Person - Phone]:[Person - Phone], [Person - Phone]@row, [Deal - Title]:[Deal - Title], [Deal - Title]@row, [Person - Email]:[Person - Email], [Person - Email]@row) = 1, 0, 1)), 0)

Now the issue is that, anytime a phone number is added that is in the format of +1 for country code, the duplicate flag is automatically checked. I've tried a few different things but can't seem to get it to work properly.

Thank you

Find more posts tagged with

Formulas

Accepted answers

Leibel S

@Devon Eddy

Seems as though when you pass in the [Person - Phone]@row into the COUNTIF function as a criteria it converts it to the value as a number (not comparing text), so "+1814552654" becomes 1814552654.

Then when it loops through your column it is keeping it as a text value of "+1814552654". It will therefore return a count of 0.

In your formula if the count is not 1 then it returns 1 and your duplicate flag is checked.

The best way to fix this is to make sure that every time a phone number is added with the + sign in the beginning it should also have something else besides the numbers (e.g. a parentheses or a dash sign).

All comments

Leibel S

@Devon Eddy

Seems as though when you pass in the [Person - Phone]@row into the COUNTIF function as a criteria it converts it to the value as a number (not comparing text), so "+1814552654" becomes 1814552654.

Then when it loops through your column it is keeping it as a text value of "+1814552654". It will therefore return a count of 0.

In your formula if the count is not 1 then it returns 1 and your duplicate flag is checked.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.