Conditional formatting to identify differences in groups of items in a list

I am trying to set up a conditional formatting rule that will highlight blocks of rows where a cell value is different from another in a group of similar items.

So for example column A has a course number.  Each course has a number of events, all given the same number.

In column B is the status. Some of those courses will show as "Evaluated" and some as "Not Evaluated".  I want to be able to highlight the courses that have a mix of "Evaluated and Not Evaluated.  The courses where all events are Evaluated or all events are Not Evaluated should not be highlighted.

An example of the resulting highlighting I'd like to achieve is attached

Grateful for any quick help here for an urgent job.

Thanks

Thanks
Capture.JPG
colin_thamesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mvidasCommented:
Hi Colin,

Choose "Use a formula to determine which cells to format". For that formula, paste in the following:

=COUNTIF($A$3:$A$14,A3)<>SUMPRODUCT(($A$3:$A$14=A3)*($B$3:$B$14=B3)*1)

Change the 14 to however many rows you have.

Matt
colin-sample.xlsx
0
colin_thamesAuthor Commented:
Brilliant, almost there but for some reason the formatted cells are one row too high.  Maybe I got something wrong.   I've attached an example (you'll see that the columns are actually J and K)

I've set the bottom row as 10,000, so the formula I've used is now:
=COUNTIF($J$2:$J$10000,J2)<>SUMPRODUCT(($J$2:$J$10000=J2)*($K$2:$K$10000=K2)*1)
Result.JPG
0
mvidasCommented:
Hmm.. I can't see any reason why that would be that way. Could J16 be slightly different somehow? Like with a space before or after 8642, or with a decimal being hidden or something?

Does   =J16=J17     return TRUE?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

colin_thamesAuthor Commented:
No, this seems to be an issue all the way through the table.  We've checked and teh numbers are identical.  No spaces, no decimals, no rounding.  The conditional format highlighting is one row out.

I'm afraid I'm not smart enough to understand how your formula works.

Any ideas?
0
mvidasCommented:
The formula is pretty simple once you break it down, so I'm guessing the problem lies in where the conditional formatting was first entered (if it was in row 1 or row 3, it could skew the data one row since it refers to row 2).  You could try to clear the current formatting, and then re-enter it.

For what it's worth, here's how the formula works. Essentially it consists of two parts
COUNTIF($J$2:$J$10000,J2)
and
SUMPRODUCT(($J$2:$J$10000=J2)*($K$2:$K$10000=K2)*1)

The first part counts the number of cells in J that equal J2. For offering 8642 this equals 16.
The second part returns a count of cells in J that equal J2 AND cells in K that equal K2. For 8642 / Not Evaluated this is 4. I can break this SUMPRODUCT formula down further if you'd like. In newer versions of excel, the COUNTIFS() function would do the same thing. I'm just an old dog that forgets the new tricks sometimes.
Then the formula asks if the two formulae return a different result ("<>" means "not equal"). In row 2's case, it looks to see if 16 is not equal to 4. Since they are not equal, it returns a TRUE value, which is what triggers the conditional formatting.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
colin_thamesAuthor Commented:
Hi mvidas
You star!  You were quite right.  We'd highlighted the column so it threw out the highlighting by a row.

Thanks also for the explanation of how the formula works.  I've learned something as well as having a problem solved.

I wouldn't have thought of using countif but having read the explanation it's a brilliant way to go.

Major kudos to you and, of course, points!

Colin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.