# Check a box for duplicate rows, if also duplicate responses in another column

✭✭

Hi!

Hoping I can get some tips on a formula I am struggling with. I am trying to mark a check box based on if I have duplicates, based on two columns.

I am trying to identify duplicates between two rows by doing the following; (1) check entire sheet in one column for the same employee ID number (EID), and then for the rows with a duplicate EID check to see if the rows with the same EID also contain the same ID Color (ID Color).

• ✭✭✭✭✭✭

You would use an IF/COUNTIFS combo like so:

=IF(COUNTIFS(EID:EID, @cell = EID@row, [ID Color]:[ID Color], @cell = [ID Color]@row)> 1, 1)

«1

• ✭✭✭✭✭✭

You would use an IF/COUNTIFS combo like so:

=IF(COUNTIFS(EID:EID, @cell = EID@row, [ID Color]:[ID Color], @cell = [ID Color]@row)> 1, 1)

• ✭✭✭✭✭

Can you share a screenshot of some sample sheet? Makes it easier to build a quick sheet on my end..

• ✭✭✭✭✭✭

If I am understanding correctly the below formula should work. You will need to replace with your actual column names. You will also need to make sure the row is a checkbox row type.

=IF(COUNTIFS([Employee ID]:[Employee ID], [Employee ID]@row, [Color ID]:[Color ID], [Color ID]@row) > 1, 1, 0)

• ✭✭

Thank you so much for the help, and so quickly! This helped me so very much.

• ✭✭✭✭✭✭

Happy to help. 👍️

• ✭✭✭✭✭

@Paul Newcome to build onto this, how could I tweak the formula to not check the first combination but only to identify the ones that are duplicates? I want to be able to filter out the duplicates but keep the original pairing.

• ✭✭✭✭✭✭

@Nate H You would need to incorporate an auto-number column with no special formatting or a system generated Created (date) type column.

Your IF statement would then change from COUNTIFS to a MIN/COLLECT.

=IF([Auto-Number]@row > MIN(COLLECT([Auto-Number]:[Auto-Number], [Employee ID]:[Employee ID], [Employee ID]@row, [Color ID]:[Color ID], [Color ID]@row), 1)

• ✭✭✭✭✭

@Paul Newcome Thank you for your response. I first tried a Created date column and got an #incorrect argument set error. I then tried the auto number approach and got an #unparsable error.

=IF([Row ID] > MIN(COLLECT([Row ID]:[Row ID], [Primary Column]:[Primary Column], [Primary Column]@row, [Practice Meeting Date]:[Practice Meeting Date], [Practice Meeting Date]@row), 1)

• ✭✭✭✭✭✭

You're missing the "@row" after the first [Row ID] reference.

• ✭✭✭✭✭
edited 07/29/24

Ah. Nice catch, @Paul Newcome. I'm still getting an #incorrect argument set:

=IF([Row ID]@row > MIN(COLLECT([Row ID]:[Row ID], [Primary Column]:[Primary Column], [Primary Column]@row, [Practice Meeting Date]:[Practice Meeting Date], [Practice Meeting Date]@row), 1)

• ✭✭✭✭✭✭

There is a missing closing parenthesis from my original example. Sorry about that.

=IF([Row ID]@row > MIN(COLLECT([Row ID]:[Row ID], [Primary Column]:[Primary Column], [Primary Column]@row, [Practice Meeting Date]:[Practice Meeting Date], [Practice Meeting Date]@row)), 1)

• ✭✭✭✭✭

Once again, sir, you have come through for me. Much appreciated!

• ✭✭✭✭✭✭

Happy to help. 👍️

• @Paul Newcome I was trying to tweak your formula

=IF(COUNTIFS(EID:EID, @cell = EID@row, [ID Color]:[ID Color], @cell = [ID Color]@row)> 1, 1)

But instead of finding duplicate ID Color, what if I wanted to find of the matched EID, check the box if ID Colors are different? I replaced the = with <>= and I'm getting an error.

• ✭✭✭✭✭✭

@Trang Turtletraxx So if I have two rows containing the same [EID] but have different [ID Color] data, you want to flag those two rows?

That would look like this:

=IF(COUNTIFS(EID:EID, @cell = EID@row, [ID Color]:[ID Color], @cell <> [ID Color]@row)> 0, 1)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!