Identify duplicate entries using Email column (contact) and Row ID column (text/number)

pris

I have a sheet we are using for attendance for multiple trainings. Each entry has a Row ID unique to that training and I want to identify duplicates of when an email entered multiple entries for one Row ID. I used the following formula in a text/number duplicate column but it is coming up with 0

=COUNTIFS([Email]:[Email], [Email]@row, [Row ID]:[Row ID], [Row ID]@row)

I tried to also make the duplicate a checkbox and use the following formula but it also isn't working. I appreciate the help.

=IF(COUNTIFS([Email]:[Email], [Email]@row, [Row ID]:[Row ID], [Row ID]@row) > 1, 1, 0)

JBG

@pris,

The first formula is correct. That should count duplicates. You are sure there are duplicates in your data. See screenshot

MedaUser

Hi @pris,

Your first formula should have worked as I was able to make it work. The only think I'd note is that your syntax is wrong for the [Email]@row, whereas it should be Email@row. Maybe that is the simple fix?

Here's a link to a published sheet I created to test this for you to let me know if I missed something in my evaluation:

https://app.smartsheet.com/b/publish?EQBCT=ccee9637b9d04132945501232b4aa254

Travis C, PMP

Smartsheet Leader with 5+ years of SS experience

If you were happy with my answer, please upvote and mark my response as answered. 

pris

I am sure there is duplicates but it isnt working. The ROW ID has leading Zeros, could that be why?

pris

@MedaUser I also noticed your email is not a contact column, could that also be playing a role?

MedaUser

@pris Thank you for the additional information. It is not the Contact column that's disrupting your formula (see my shared sheet as I added that column for testing). It turns out to be your training ID column that's likely disrupting your formula. I assume you are adding the 00011 manually for each row, which will add an apostrophe to the beginning of the leading zeros to retain your intended value as displayed with leading zeros. This seems to be causing the issue for me as well.

I would suggest that you either remove your leading zeros and make the number "11" or add a letter to the beginning of your training identifier, such as TRN00011.

Smartsheet Source: In a Text/Number column, if the first character in a cell is zero, followed by another digit or letter, Smartsheet attaches an apostrophe to the beginning of the value. This is done to retain and display the leading zero as part of the number. If you want to store the value as a number to perform calculations, you need to double-click the cell and remove the apostrophe and any leading zeros. For more information, please visit: .

https://help.smartsheet.com/articles/2480241-column-type-reference

Travis C, PMP

Smartsheet Leader with 5+ years of SS experience

If you were happy with my answer, please upvote and mark my response as answered.