Highlighting Duplicates in two seperate columns

Greg K

Seems a simple enough function (of which many many many people have already asked) so I spent the last 3 hours trying to find and copy a formula to work - to no avail.

Simply trying to have Any value in column "a", which is also found anywhere (not just in the same row adjacently) in column "b" highlighted - or check boxed with a helper column.

Please and thanks

Paul Newcome

Try something along the lines of the below in a checkbox column...

=CONTAINS([Column A]@row, [Column B]:[Column B])

Greg K

Thank you kindly Paul,

Unfortunately grrrrr. "#UNPARSEABLE"

Here is the formula I tried as this is the names of the columns. I tried the formula in a check box "helper" column? Is that where I should have tried it?

=CONTAINS([Column 5]@row, [PARID Delq]:[PARID Delq])

Greg K

Paul, You are the man. I stand corrected. I got it to work;

=CONTAINS([Column5]@row, [PARID Delq]:[PARID Delq])

Yay!

Thanks!

Paul Newcome

Happy to help. 👍️

Leah Ricciuti

I am trying to find duplicate names in a sheet and to have the row highlighted if there are duplicate values.

The individuals are listed on different rows - the first and last names are separated into different columns (along with their email, and phone numbers). I want to find the people that have the both the same last name and the same first name.

=IF(CONTAINS([Last name]1@ROW,[Last name]1:[Last name]1),1,0)

David Joyeuse

Hi @Leah Ricciuti

I would suggest you to add 2 columns in order to do this:

• First one is Full Name with this formula: =JOIN([Last Name]@row:[First Name]@row, " ").
• Second one is just a checkbox with this formula within it: =IF(COUNTIF([Full Name]:[Full Name], [Full Name]@row)>1,1,0)

First one will concatenate both First and Last Name.

Second will scan the Full Name range and will count duplicates. If a Full Name appears more than once, the box will be checked.

Then, do some conditional formatting based on the checkbox to highlight the full row.

Paul Newcome

@Leah Ricciuti You shouldn't need the column to pull first and last names together. You should be able to use something like this in your flag column:

=IF(COUNTIFS([First Name]:[First Name], [First name]@row, [Last Name]:[Last Name], [Last Name]@row) > 1, 1)

David Joyeuse

Yeah, @Paul Newcome is probably right that should also be working.

Purely technical, which way of doing it should be the fastest for the sheet? I always like to have formulas that doesn't slow the sheet too much. It seems both would do the same number of calculation, but with one column less to display, so that's still that to give the edge to Paul's solution.

Paul Newcome

Less to calculate and one less column I would imagine would be a little more efficient on the back-end. It is also one less thing to manage/break.