including is not blank in a function

06/07/21
Accepted

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

Best Answers

  • Bassam KhalilBassam Khalil ✭✭✭✭✭
    Accepted Answer

    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:


    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful". 

  • Accepted Answer

    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

  • Accepted Answer

    awesome, worked like a charm!

Answers

  • Bassam KhalilBassam Khalil ✭✭✭✭✭
    Accepted Answer

    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:


    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful". 

  • 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")


  • Accepted Answer

    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å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, [email protected], 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!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Accepted Answer

    awesome, worked like a charm!

  • Andrée Starå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!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.