compare 3 values from 3 different cells and COLOR which cell is not matching

[aayaanmayank]

Hi Can someone help me with formula.

compare texts from 3 different cells and COLOR which cell is not matching from remaining 2

 

[Eric W]

You could use Conditional Formatting. If your 3 cells are A1:C1, then select those 3 cells, click Conditional Formatting > New Rule > Use a formula > and enter:

=COUNTIF($A$1:$C$1,A1)=1

and select a fill color. It's slightly more complicated if the cells are disjoint, or if you have to account for empty cells.

 

[aayaanmayank]

Hi Thanks for you reply. But in this case it only comparing cell A1 to B2 and C2. However i need if any of the cell is different from then it should highlight that cell. Like A1& B2 had same text but C2 had different text so it should highlight C2.

 

[aayaanmayank]

or A1 and C2 has same text but b2 has different text

 

[Eric W]

Did you try it?

The formula will do that. Keep in mind that formulas in Conditional Formatting also follow the absolute/relative reference rules. The entire range has $A$1:$C$1 with the $ signs indicating absolute references. The A1 in the second parameter does not have the $ signs, meaning that in the B1 and C1 cells it changes to B1 and C1. You must select all 3 cells when entering the formula. Try that and let me know how it works.

 

[aayaanmayank]

Yes its working thanks

 

[Eric W]

You're welcome. Thanks for the feedback.

 

[aayaanmayank]

Hi Eric can you check and advise that why it is not working when below string is in a2, b2 & c2. I made one change in formula i am using =COUNTIF($A:$C,A1)=1
[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]Chirag[/TD]
[TD="width: 64"]Chirag[/TD]
[TD="width: 64"]Mayank[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Are you trying to use the formula on the entire column? If so, the COUNTIF won't work, since it will try to look at the whole column, and not just the row you're on. Instead, you'd have to try this: Select columns A:C. Click Conditional Formatting > New Rule > Use a formula, and this formula:

=AND(A1<>"",(A1=$A1)+(A1=$B1)+(A1=$C1)=1)