Detecting same value in 3 cells

[Excript]

Need some help.

Would like to detect same value in 3 cells. Can anyone come up with a solution to that.

Q 16, R 16, S 16

If there is a same value appearing in two of the three cells... it will acknowledge me.

[JBeaucaire]

Acknowledge to you how? You want them to light up/change color? Or a cell value in another column gives you some sort of warning like "Bad Match!"...? What do you want to happen?

[Excript]

Yes, the cell to change color

[zbor]

OK, so.. if Q 16 = R 16 = S 16 everything is OK.

Do you need if any value is different to change color in all 3 cells or only in cell that is different from other two?


If all 3 are different the collor will be changed in all 3...

[Excript]

thanks zbor for your reply.

for example.

Q16 = 1
R 16 = 2
S 16 = 1
or

Q16 = 1
R 16 = 1
S 16 = 2

in these 3 cells as long as there's a duplicate found in 2 of the 3 cells, it will acknowledge me in a way. Pls help

[zbor]

New post...

[zbor]

Something like this?

Try to change numbers in cells...

Attachment 38598

If all are equal green, otherwise red... (QRS)

If all are equal green, one different red, all different all red (HIJ)

[JBeaucaire]

The Conditional Format is close, but needs a little tweaking:

Q16: =AND(Q16<>0,Q16<>R16,Q16<>S16)
R16: =AND(R16<>0,R16<>Q16,R16<>S16))
S16: =AND(S16<>0,S16<>Q16,S16<>R16))

Sample attached.

[zbor]

Why.. can't they all be 0=0=0

Did you upload right document?

[JBeaucaire]

Misread the original post. Corrected above, text and workbook.

He should have the idea by now...

[Excript]

Zbor, what did you do to it work like the one you posted?
can you teach me?

[zbor]

It's all about conditional formating...

I'll do step by step... Don't mind me for repeating things that you already know...

In HIJ I used OR formula and colors...

For deafult cell color of all 3 cells I used red Attachment 38700

After that (again for all 3 cells) I used conditional formating...

Conditional Format -> New rule -> Use a formula to determine wich cells to format:

Now you need a formula: what do you want to do...

In this case you want that: if that cell is same like some other cell it will become green... If not it will stay red...

so..

17 16 17 - green red green
16 17 17 - red green green
14 14 14 - green green green
14 16 17 - red red red and so on...

Attachment 38701

For this case formula is:
H cell: =OR(H19=I19; H19=J19)
I cell: =OR(I19=H19;I19=J19)
J cell: =OR(J19=H19;J19=I19)

Comparing each cell with any other cell... (if there is more cell, let say 30 or 100, this would be hard to do, you would need other solution)...

If you need all cells to become red or green principle is the same, you only need other formula:


Q cell: =AND(Q16=R16;Q16=S16)
R cell: =AND(Q16=R16;R16=S16)
S cell: =AND(S16=R16;Q16=S16)

Enjoy

Edit: please put this thread as solved if you satisfied with solution