Highlighting Duplicates in two seperate columns
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
Answers

Try something along the lines of the below in a checkbox column...
=CONTAINS([Column A]@row, [Column B]:[Column B])

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

Paul, You are the man. I stand corrected. I got it to work;
=CONTAINS([Column5]@row, [PARID Delq]:[PARID Delq])
Yay!
Thanks!


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)

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.

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

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.

Less to calculate and one less column I would imagine would be a little more efficient on the backend. It is also one less thing to manage/break.
Help Article Resources
Categories
Check out the Formula Handbook template!