Duplicates in Column

MindyM

I used the formula below in my sheet and it seems to be working alright except that it's coming back with a couple saying they're duplicates but when I use control find to see if that eID is a duplicate on my sheet, it's the only one. Is there something wrong with my formula?

=IF(COUNTIF(eID:eID, eID@row) > 1, 1, 0)

Peggy Parchert

Hello @MindyM

The helper column is an # auto number column type. It should populate for all existing rows and any new rows. You might have to refresh your sheet to see the population.

Peggy

Peggy Parchert

Hello @MindyM

Happy Holidays! I would add a helper column (Column Type: # auto number) with no specifics. This can be hidden after created. Then enter the below formula into your Duplicate (Column Type: checkbox).

=IF(eID@row <> "", IF(COUNTIFS(eID:eID, @cell = eID@row, helper:helper, @cell <= helper@row) >= 2, 1, 0))

Hope this helps.

Peggy

MindyM

Hi @Peggy Parchert!

Happy Holidays! Thank you! That worked but the issue I have now is how do I get the helper column to add in the numbers all the way down? I set it up to do auto # but I can't pull down and have the numbers advance it just copies 1 all the way down the column. I have over 1,000 rows and I'm sure there's an easier way than me typing 1, 2, 3, ,4 in each row.

Thanks again!

Peggy Parchert

Hello @MindyM

The helper column is an # auto number column type. It should populate for all existing rows and any new rows. You might have to refresh your sheet to see the population.

Peggy

MindyM

Oh my, I feel special. I wasn't sure how to make that work after you changed the column type. Thank you! That worked!

Peggy Parchert

@MindyM - happy to help - have a great holiday!

Peggy