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!
-
Happy to help. 👍️
-
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 back-end. It is also one less thing to manage/break.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!