Duplicates

Adriane Price
Adriane Price ✭✭✭✭✭✭

Hello ~ I am creating a couple columns to be able to identify duplicates with Name entries and Job Posting entries. However, when creating the formula for the "Duplicate" column I see that it is not flagging the duplication. I believe it may be in the way I have written the formula and was hoping for some guidance.


=IF(AND(COUNTIFS([Duplicate Name Check]:[Duplicate Name Check], [Duplicate Name Check]@row, [Duplicate Job Posting Check]:[Duplicate Job Posting Check], <>"") > 0, [Duplicate Job Posting Check]@row = ""), true, false)

The area where I put in bold I think may be my issue but not 100% sure. Below screenshot is where I put the formula (just in case curious).

Thank you!! πŸ˜„

Adriane

Tags:

Best Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer βœ“

    Hi @Adriane Price ,

    For the duplicate name flag try: =IF(COUNTIF([candidates first name]$1:[candidates first name]@row)>1, 1, 0). This should leave the 1st occurrence unflagged but flag the 2nd and greater.

    If there is a first and last name you'd do a COUNTIFS with both criteria.

    Do the same for job posting.

    Duplates would be those with both name and job posting flagged as duplicate.

    Is that what you need to do?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer βœ“

    Hi Adriane,

    Ugh, I gave you the wrong formula to start. In your duplicate name check column use this formula:

    =IF(COUNTIF([Candidate First name]$1:[Candidate First Name]@row, =[Candidate First Name]@row) > 1, 1, 0)

    Do the same for duplicate job posting with:

    =IF(COUNTIF([Job Posting ID]$1:[Job Posting ID]@row, =[Job Posting ID]@row) > 1, 1, 0)

    If you're intent is to flag as duplicate if both name and job posting are duplicates then your formula in the Duplicate column would be:

    =IF(AND([Duplicate Name Check]@row =1, [Duplicate job posting ID]@row=1),1,0)

    Work this time?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer βœ“

    Hi @Adriane Price ,

    For the duplicate name flag try: =IF(COUNTIF([candidates first name]$1:[candidates first name]@row)>1, 1, 0). This should leave the 1st occurrence unflagged but flag the 2nd and greater.

    If there is a first and last name you'd do a COUNTIFS with both criteria.

    Do the same for job posting.

    Duplates would be those with both name and job posting flagged as duplicate.

    Is that what you need to do?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Adriane Price
    Adriane Price ✭✭✭✭✭✭

    @Mark Cronk it did not seem to like that syntax I also tried it a couple different ways.


    This one seemed to work

    However, my column "Duplicate" is still not flagged.

    There is a first and last name and I will be doing the 'COUNTIFS' for both criteria as well as Job Posting ID.

    Sorry, it took so long to respond.


    Thank you!

    Adriane

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer βœ“

    Hi Adriane,

    Ugh, I gave you the wrong formula to start. In your duplicate name check column use this formula:

    =IF(COUNTIF([Candidate First name]$1:[Candidate First Name]@row, =[Candidate First Name]@row) > 1, 1, 0)

    Do the same for duplicate job posting with:

    =IF(COUNTIF([Job Posting ID]$1:[Job Posting ID]@row, =[Job Posting ID]@row) > 1, 1, 0)

    If you're intent is to flag as duplicate if both name and job posting are duplicates then your formula in the Duplicate column would be:

    =IF(AND([Duplicate Name Check]@row =1, [Duplicate job posting ID]@row=1),1,0)

    Work this time?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi Adriane,

    Glad you found a solution. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!