Duplicates

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

Mark Cronk

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

Mark Cronk

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

Mark Cronk

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

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!

Mark Cronk

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

Mark Cronk

Hi Adriane,

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

Mark