Conditional Formatting based on 2 column data

**tobbrack** · 10-01-2011, 07:32 AM

I've searched and cannot quite find what I am trying to do (though it's probably extremely easy). I can highlight a single column no problem but I need to highlight names within a column based on both the name and a number on the cell adjacent to each name.

I have a column with names in it and an adjacent column with numbers in it and I'm trying to highlight duplicates that have the same name and the same number on a row. So say there are three Brian's in the name column and ajdacent to the Brian's only two numbers are the same, I only want these two Brian's to be highlighted (as below).

Tom 12
Brian 18
Mary 11
Joe 16
Anne 15
Tom 14
Brian 14 <--- so this one would not be highlited as the name and number don't match
Mary 12
Joe 13
Anne 19
Tom 15
Brian 18
Mary 17
Joe 12
Anne 13

I'm using Excel 2010 - could anyone help me figure this out? Thanks for any help!

**zbor** · 10-01-2011, 07:51 AM

Add one more column in C1 (=A1&B1) then use this formula in CF: =COUNTIF($C$1:$C$1000, $C1)>1

(you can hide C column if you like)

**Marcol** · 10-01-2011, 08:03 AM

Your profile indicates you are using 2003, but your post states you are using 2010.

This should work for both versions
Assuming your data is in Columns A:B begining row 2
Formula is :=

=AND($A2<>"",SUMPRODUCT(--($A$2:$A$1000=$A2),--($B$2:$B$1000=$B2))>1)

Applies to:=

=$A$2:$B$1000

**tobbrack** · 10-01-2011, 08:27 AM

Originally Posted by zbor

Add one more column in C1 (=A1&B1) then use this formula in CF: =COUNTIF($C$1:$C$1000, $C1)>1

(you can hide C column if you like)

Thanks for the help - I tried the way below and it worked like a charm but cheers for the response though! Thanks!

Originally Posted by Marcol

Your profile indicates you are using 2003, but your post states you are using 2010.

This should work for both versions
Assuming your data is in Columns A:B begining row 2
Formula is :=

=AND($A2<>"",SUMPRODUCT(--($A$2:$A$1000=$A2),--($B$2:$B$1000=$B2))>1)

Applies to:=

=$A$2:$B$1000

This worked like a treat - absolutely fantastic! I have been doing this manually for a very long time but this will save me hours. Thanks!!

Conditional Formatting based on 2 column data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Conditional Formatting based on 2 column data

Re: Conditional Formatting based on 2 column data

Re: Conditional Formatting based on 2 column data

Re: Conditional Formatting based on 2 column data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions