Conditional Formatting based on similar rows

Question

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

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?

If only one number matches, are you ok with highlighting more than one row? E.g. can 4 and 5 be both highlighted red, or only 4? — BigBen
– BigBen, Commented Jun 21, 2019 at 3:49
@BigBen Having both 4 and 5 highlighted would be fine as long as it is red in this case — brent_mb
– brent_mb, Commented Jun 21, 2019 at 4:01
Is a helper column a possibility? Also can there be a number 1 and number 2 on the same row, or is it always one or the other? — BigBen
– BigBen, Commented Jun 21, 2019 at 4:06
@BigBen Yes adding additional columns to mark off which VLOOKUP's are successful is allowed (if that's what you had in mind). Or something similar — brent_mb
– brent_mb, Commented Jun 21, 2019 at 4:08

BigBen · Accepted Answer · 2019-06-21 04:31:23Z

1

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)

answered Jun 21, 2019 at 4:31

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

brent_mb Over a year ago

Nice. I will work to improve a bit and maybe get rid of the helper columns, but this works, so thank you.

BigBen Over a year ago

No problem, glad to help. Late here so I left some cleanup to you :)

Collectives™ on Stack Overflow

Conditional Formatting based on similar rows

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related