CODEWITHSUNDEEP
excelvlookupconditional-formatting
brent_mb
brent_mb

Reputation: 337

Conditional Formatting based on similar rows

Please note that I have included the link to the excel file because describing the problem is a bit tough. I think seeing the file may help clarify things.

Matching & Highlighting Sheet

enter image description here I am trying to do something that requires somewhat, (at least for me), complex logic using simple functions in Excel. I have 2 tables, Group 1, and Group 2. Each Group has a list of people that are listed out more than once. Each person has an ID, a Number 1 side, and a Number 2 side. I also have a simple one column table called 'Key' that just contains the values I am searching for.

So, what I am trying to do is highlight the entire row of a person who has one of their numbers, either number 1 or number 2, in red, and somebody that has all of their numbers matching with any value in the 'Key' table to be highlighted in green.

I have attached my Excel file to make this easier to follow.

I am doing this on a small scale, and I have had some success using a series of IF statements with embedded VLOOKUPS, and some more success with using IFERROR's with embedded VLOOKUP's, but none of my solutions really work tooo well.

Does anyone have any advice or a solution?

Upvotes: 0

Views: 37

Answers (1)

BigBen
BigBen

Reputation: 49998

With a helper column or two, here's one option. I'm sure it can be improved, but I'll leave that to you or someone else.

In column F:

=OR(AND(D4<>"",COUNTIF($C$24:$C$38,D4)),AND(E4<>"",COUNTIF($C$24:$C$38,E4)))

In column G:

=IF(COUNTIFS($C:$C,$C15,$F:$F,TRUE),COUNTIFS($C:$C,$C15,$F:$F,TRUE)=COUNTIF($C:$C,$C15),"")

Green conditional formatting, based on the formula:

=$G4

Red conditional formatting, based on the formula:

=NOT($G4)

enter image description here

Upvotes: 1

Related Questions