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

Gary_

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).

Any suggestions would be helpful.

Paul Newcome

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)

Paul Newcome

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)

Cory Page

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

Hollie Green

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)

Gary_

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

Paul Newcome

Happy to help. 👍️

Nate H

@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.

Paul Newcome

@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)

Nate H

@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)

Paul Newcome

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

Nate H

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)

Paul Newcome

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)

Nate H

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

Paul Newcome

Happy to help. 👍️

Trang Turtletraxx

@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.

Paul Newcome

@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)