including is not blank in a function

DHR Temp Assignment Team

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!!!!

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!

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:

Toufong Vang

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

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

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!