Highlight cells if data in 2 columns match

**damobilebrood** · 05-27-2014, 03:14 PM

Hello All,
Please see the attached example. I'm trying to highlight cells only if 2 values in the row match 2 values in another row. If a book title/author combination matches that of another title/author combination in a different row, it would highlight. Some alternate rows will contain different titles by the same author, and some rows will have like titles by different authors...but they should not highlight.
The only situation where highlighting would occur is if the title/author pairing appears in another row.
I'm trying to decide if this would be a conditional formatting/highlighting rule, or if it would be a macro/vbs?
Any help would be greatly appreciated.
I'm using Excel 2013.
Thanks, in advance!

Pairing example.PNG

**etaf** · 05-27-2014, 03:31 PM

try
=COUNTIFS($D$2:$D$15,$D2, $E$2:$E$15,$E2)>1

**PCI** · 05-27-2014, 03:37 PM

Use a conditional format with next formula, for D2 and E2, then copy and special paste as you need
=SUMPRODUCT(--((($D$2:$D$10 )& ($E$2:$E$10))=(D2&E2)))>1

**JOHN H. DAVIS** · 05-27-2014, 03:39 PM

Maybe:

Please Login or Register  to view this content.

**damobilebrood** · 05-27-2014, 04:29 PM

Thanks for the responses!
@eTaf: Your formatting rule works, but it's missing cells at random. I don't know what's triggering them, but they remain unhighlighted even though they have a match.
@PCI: Thanks, but this isn't really ideal to copy and paste as I am working with roughly 30k rows.
@John: Your vba starts working, then locks up Excel. It freezes and I have to force it closed.

I'm still taking suggestions.

**etaf** · 05-27-2014, 04:31 PM

Your formatting rule works, but it's missing cells at random. I don't know what's triggering them, but they remain unhighlighted even though they have a match.

I doubt its a random thing - there will be a difference
one may be spaces or hidden characters
Often there is a space at the end of some of the text and of course
TEXT....[space] is different to TEXT....[nospace]

check and see if that maybe the issue - or can you post a sample that is not working here - so we can see

**PCI** · 05-27-2014, 04:36 PM

"this isn't really ideal to copy and paste as I am working with roughly 30k rows"
High light the 2 first cells and do the copy (Control + c)
Press Shift and Control + Down arrow
you will cover the complete column
then do the special paste FORMAT

**etaf** · 05-27-2014, 04:40 PM

done, see attached
works OK for that file

**damobilebrood** · 05-27-2014, 04:48 PM

@etaf: Here's a sampling.

Edit: Oops. Didn't see your responses before I posted.

**damobilebrood** · 05-27-2014, 04:56 PM

@PCI: Thanks, I'm tinkering more with your suggestion. I totally forgot that I could paste the formatting apart from the values. DUH! Sorry.

**etaf** · 05-27-2014, 05:01 PM

the apply range is
=$D$2:$E$4,$D$6:$E$9,$D$11:$E$17,$D$19:$E$19,$D$21:$E$1016959
so missing some rows

try using
=COUNTIFS($D:$D,$D1,$E:$E,$E1)>1
and apply to
=$D:$E

see sample below

**PCI** · 05-27-2014, 05:10 PM

I could be more clear and updated the answer: Special Paste FORMAT

**etaf** · 05-27-2014, 05:13 PM

I could be more clear and updated the answer: Special Paste FORMAT

sorry, I have since seen the issue in your sample and replied with a fix

is the original still an issue if you delete all the conditional formatting and just use the new formula and apply range ?

**JOHN H. DAVIS** · 05-28-2014, 06:36 AM

Originally Posted by damobilebrood

Thanks for the responses!
@eTaf: Your formatting rule works, but it's missing cells at random. I don't know what's triggering them, but they remain unhighlighted even though they have a match.
@PCI: Thanks, but this isn't really ideal to copy and paste as I am working with roughly 30k rows.
@John: Your vba starts working, then locks up Excel. It freezes and I have to force it closed.

I'm still taking suggestions.

Worked when I tested it on your sample:

Try:

Please Login or Register  to view this content.

**damobilebrood** · 05-28-2014, 09:19 AM

Sorry for the late response. I'm back at it.
@etaf: The range was showing weird values because I just took a sampling from my actual sheet. Like I said, I have about 30,000 rows. When I clipped the rows for the sample it adjusted the range shown in the formula. In the original sheet, the range is D2 to D28,200 and E2 to E28,200.
@John: I do see your VBA start to work, but then crash. I honestly think it's because of the size of the sheet that it hangs.

I managed to get the desired results, though, by using a combination of your suggestions.
Thank you so much for taking the time to reply! It is much appreciated!

Highlight cells if data in 2 columns match

LinkBack

Thread Tools

Rate This Thread

Display

Highlight cells if data in 2 columns match

Re: Highlight cells if data in 2 columns match

Re: Highlight cells if data in 2 columns match

Re: Highlight cells if data in 2 columns match

Re: Highlight cells if data in 2 columns match

Re: Highlight cells if data in 2 columns match

Re: Highlight cells if data in 2 columns match

Re: Highlight cells if data in 2 columns match

Re: Highlight cells if data in 2 columns match

Re: Highlight cells if data in 2 columns match

Re: Highlight cells if data in 2 columns match

Re: Highlight cells if data in 2 columns match

Re: Highlight cells if data in 2 columns match

Re: Highlight cells if data in 2 columns match

Re: Highlight cells if data in 2 columns match

Thread Information

Users Browsing this Thread

Similar Threads

highlight if cells DONT MATCH

[SOLVED] compare values in two cells, highlight when there is no match.

[SOLVED] Need to match and highlight cells with similar (not same) data

Highlight the cells that match??

Match data in 2 Worksheets and highlight columns

Bookmarks

Bookmarks

Posting Permissions