Text Conditional formatting if cell not equal to another

[Paul Naylor]

Hoping someone can help?

Need to identify some variances in columns of text data . Although multiple columns I don't want to use multiple helper columns and thought easiest & most visually striking way is to use conditional formatting to highlight using a colour any differences in the 2nd column of data.

1st column C3-C47 contains manager call observation results to various questions ( text) and 2nd column G3-G47 contains quality team call observations results to the same set of questions.

All I would like is for any of the answers in the 2nd set of data are not equal to 1st set is for the relevant answer in the 2nd set to be highlighted in a colour.

I've tried to do this with conditional formatting , but all methods I've looked at seem to need an extra column , but with potentially 40 different comparisons spreadsheet will be unusable.

Anyone any ideas of how to do?

E.g. 2 columns of data on call observations answers to a number of questions. One set say in column C is managers observations and 2nd set say in column G is quality teams observations. Need to be able to identify by any differences between the managers observations and

 

[Paul Naylor]

Hi Etaf,

Just to put this in context . These are results of call quality checking , with 1st 3 e.g. A,B&C columns the results of manager checks ( call 1,2& 3) and next 3 columns e.g. E,F&G the results of the quality checks on the same 3 calls which is why I'm comparing column A to D, column B to E & Column C to F. In terms of row 33 it's 1 if quality team have marked the same call and 0 if they haven't with formula just filling "--" in the relevant column for non quality teams markings.

what I am looking to be able to do is highlight both manager & quality teams individual answers if different but only if Quality have marked the call as I'm trying to identify variances between manager and quality teams markings, also as an added bonus it would be good if I had an extra row e.g. 34 , which counted the number of variances either by colour or formula?

 

[Paul Naylor]

Hi Etaf, just rechecking s/sheet also noticed that there is an extra condition in row 33, the value could also be non-numeric as if only 1 or 2 calls scored by manager the cell could equal "--", not sure if this makes a difference?

 

[etaf]

OK, I'm following then as follows

A > D
B > E
C > F

so they are compared and all should be the same .

Now we are looking for a 1 in row 33
and only doing the comparison if there is a 1 in the row

So to check A > D then both row33 for A and D need to be 1

=And( A2=D2, A$33=1,D$33=1)

I have set the same rule - BUT for different selections

otherwise we are checking F against I and was not sure how we would highlight F if did not match C when conditional formatting is checking F -

I'm sure can be done with 1 formula - but i could not work it out

so i selected A5:C33
and used
=AND( A5<>D5, A$33=1,D$33=1)

then i selected D5:F33
and used the same formula
=AND( A5<>D5, A$33=1,D$33=1)

that way it only checks the values between A to F

see hat you think

Book1
	A	B	C	D	E	F
1
2
3
4
5	Yes	Yes	Yes	Yes	Yes	Yes
6	No	Yes	Yes	No	Yes	Yes
7	No	Yes	Yes	Yes	Yes	Yes
8	Yes	Yes	Yes	Yes	Yes	Yes
9	Yes	Yes	Yes	Yes	Yes	Yes
10	Yes	Yes	Yes	Yes	Yes	Yes
11	Yes	Yes	Yes	Yes	Yes	Yes
12	Yes	Yes	Yes	Yes	Yes	Yes
13	Yes	Yes	N/A	N/A	N/A	N/A
14	Yes	Yes	Yes	Yes	Yes	Yes
15	Yes	Yes	N/A	Yes	Yes	N/A
16	Yes	Yes	N/A	Yes	Yes	N/A
17	Yes	Yes	Yes	Yes	Yes	Yes
18	Yes	Yes	N/A	Yes	Yes	N/A
19	Yes	Yes	N/A	Yes	Yes	N/A
20	N/A	No	N/A	N/A	No	N/A
21	Yes	Yes	Yes	Yes	Yes	Yes
22	Yes	Yes	N/A	N/A	N/A	N/A
23	Yes	Yes	Yes	Yes	Yes	Yes
24	Yes	Yes	Yes	Yes	Yes	Yes
25	Yes	Yes	Yes	Yes	Yes	Yes
26	N/A	Yes	N/A	N/A	Yes	N/A
27	Yes	Yes	Yes	Yes	Yes	Yes
28	N/A	N/A	N/A	N/A	N/A	N/A
29	Yes	Yes	Yes	Yes	Yes	N/A
30	Good	Good	Good	Good	Good	Good
31	20	90	100	90	90	100
32	Not meeting expectation	Successful Performance	Successful Performance	Successful Performance	Successful Performance	Successful Performance
33	1	1	1	1	1	1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D5:F33	Expression	=AND( A5<>D5, A$33=1,D$33=1)	text	NO
A5:C33	Expression	=AND( A5<>D5, A$33=1,D$33=1)	text	YES

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A5:C33 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND( A5<>D5, A$33=1,D$33=1)

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

AND THEN
for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
D5:F33 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND( A5<>D5, A$33=1,D$33=1)

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK