Having trouble with the duplicate formula and where to input.

Rebecca Lane

I am trying to set-up a formula, so if there is a duplicate in the Family ID column, the box becomes checked in the Duplicate column. So far, during the searching I have done for other users with this same problem, I am still left with an error message each time.

Is there a way to run the formula on the whole column, or do I need to do it for each entry?

I would appreciate any help. Thank you in advance. 😊

Paul Newcome

Try something like this...

=IF(COUNTIFS([Family ID]:[Family ID], [Family ID]@row) > 1, 1)

Put that in every row, and you should be set.

Paul Newcome

Are you just trying to check the box if the number in the [Family ID 2] column is found anywhere in the [Family ID] column?

If so, try this...

=IF(COUNTIFS([Family ID]:[Family ID], [Family ID 2]@row) > 0, 1)

Paul Newcome

Try something like this...

=IF(COUNTIFS([Family ID]:[Family ID], [Family ID]@row) > 1, 1)

Put that in every row, and you should be set.

Rebecca Lane

Thanks so much for your help Paul, it worked!

Paul Newcome

Happy to help! 👍️

Rebecca Lane

Hi Paul,

I was hoping that you would be able to assist me further. I have tried other VLOOKUP and a CONTAINS formula for the "Duplicate 3" column, but I continue to get an incorrect argument error.

I am trying to track if the entries in the "Family ID 2" are also listed in the "Family ID" column. Do have any suggestions on the correct formula used? I have tried =VLOOKUP([Family ID 2]1, [Family ID]1:[Family ID]4988, 1, (false)), but this formula changes the cell number when copied rather than looking up the whole column.

Thanks in advance.

Rebecca

Paul Newcome

Are you just trying to check the box if the number in the [Family ID 2] column is found anywhere in the [Family ID] column?

If so, try this...

=IF(COUNTIFS([Family ID]:[Family ID], [Family ID 2]@row) > 0, 1)