Original Poster

Feb 23, 2022

Identify duplicates between two columns in Google Sheets

I am looking to have column A(entire column) cells highlight if there is a match in Column B(Entire column)
I thought it would be simple but the formula I found is being inconsistent and I'm not sure how to fix it. I'm open to a totally new formula btw.

As an example if the name "John Smith" is in both columns A AND B, it would highlight the name in column A.

Here is my current formula
=countif($A:$A,$B2)>0

Thank you in advance for any help with this

Details

Sheets,Microsoft Windows,Personal use

Locked

Informational notification.

This question is locked and replying has been disabled.

Community content may not be verified or up-to-date. Learn more.

Recommended Answer

Platinum Product Expert

Feb 23, 2022

@Chad H 4212, Apologies, I misenterpeted your request and thought you wanted to highlight the entire column.

If the formula PE @Spencer provided doesn't work as expected, try this:

=countif(B:B,A1)

Original Poster Chad H 4212 marked this as an answer

Helpful?

All Replies (9)

Original Poster

Feb 23, 2022

Spencer Farris I'm trying to think of how to describe the result. I was what I was getting from my own formula. Some names highlight in column A, others do not. At first, I took this as an indication that it was working.

Upon looking at the results, some names that are in column B did not highlight. Others that shouldn't highlight did. I verified this further by picking one of the names that wasn't highlight and used the "find" feature returning 2 results.

I hope I explained that well?

Platinum Product Expert

Sheets and Apps Script

Feb 23, 2022

I've succesfully used this formula before and tested it again before posting. The only time I've seen this not work is when A and B do not exactly match. Most often I've seen an extraneous space after the text in one of the columns.

We can better help if you share the sheet itself with permissions set to "Anyone with link can edit"

Last edited Feb 23, 2022

Platinum Product Expert

Recommended Answer

Feb 23, 2022

@Chad H 4212, Apologies, I misenterpeted your request and thought you wanted to highlight the entire column.

If the formula PE @Spencer provided doesn't work as expected, try this:

=countif(B:B,A1)

Original Poster Chad H 4212 marked this as an answer

Original Poster

Feb 23, 2022

Spencer, thank you. I did try to verify spacing, but even some items (dates) in column A were highlighting when there are no dates in column B. I'm sure it was something I was doing wrong.

ztiaa/z that worked perfectly!

Thank you to both of you for taking the time to help me solve MY problem. You are great people and it's much appreciated!

Search

Clear search

Close search

Main menu