including is not blank in a function

Hi Wizards! I have a function to look for duplicate last names in a sheet; however, the cells will be blank as we start the project...it is returning "duplicate" in all of the blank cells which I don't want:

=IF(COUNTIFS([LAST NAME]:[LAST NAME], [LAST NAME]@row) > 1, "Duplicate")

I've tried to include IF([LAST NAME]<>""(rest of formula) but that is not working ...

Any help would be greatly appreciated!!!!

Find more posts tagged with

Accepted answers

Bassam Khalil

Hi @DHR Temp Assignment Team

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

=IF(ISBLANK([LAST NAME]@row), "", IF(COUNTIFS([LAST NAME]:[LAST NAME], [LAST NAME]@row) > 1, "Duplicate"))

the following screen shot shows the result:

DHR Temp Assignment Team

Hi thank you - this is great - i now need to look at the last name and first name; is it possible to have the range last name: first name and it will look at both columns to see if there are duplicates ?

Jon Doe (Duplicate)

Kathy B

Jon

Jon Doe (Duplicate

DHR Temp Assignment Team

awesome, worked like a charm!

All comments

Bassam Khalil

Hi @DHR Temp Assignment Team

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

=IF(ISBLANK([LAST NAME]@row), "", IF(COUNTIFS([LAST NAME]:[LAST NAME], [LAST NAME]@row) > 1, "Duplicate"))

the following screen shot shows the result:

TVang

Bassam's nested IF() statement will work.

However, since you're already using COUNTIFS() which lets you use multiple criteria, why not add a second criterion to what you already have there, especially since you've already identified it?

Given, you don't want to count blanks, your criterion [LAST NAME]@row <>"" is correct . Now all you need is the second range which happens to be the same [LAST NAME]:[LAST NAME].

The format for COUNTIFS() is COUNTIFS(range1, criterion1, range2, criterion2, range_N, criterion_N).

Your revised formula would be...

=IF(COUNTIFS([LAST NAME]:[LAST NAME],[LAST NAME]@row,[LAST NAME]:[LAST NAME],[LAST NAME]@row<>"" )>1,"Duplicate")

DHR Temp Assignment Team

Jon Doe (Duplicate)

Kathy B

Jon

Jon Doe (Duplicate

Andrée Starå

Hi @DHR Temp Assignment Team

I hope you're well and safe!

Try something like this.

Add a so-called helper column (I named mine Join) to join together the First and Last Names.

=[First Name]@row + [Last Name]@row

Add the following formula to the Duplicate Check column.

=IF(COUNTIFS(Join:Join, Join@row, Join:Join, <>"") > 1, "Duplicate")

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

DHR Temp Assignment Team

awesome, worked like a charm!

Andrée Starå

@DHR Temp Assignment Team

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

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.