Find the wrong value

[janu319]

Hi, can someone please help me with this query.
As shown in the attached image, for one room in one house, if the code is 100 or 101, then the other rooms in that house should not have a code 102.
I want to get those values and highlighted in the sheet.

House Number	Nu.Rooms	Code
1	1	100
2	1	101
2	2	102	Wrong
2A	1	102
2A	2	102
2A	3	102
2A	4	102
3	1	103
4	1	102	Wrong
4	2	101
4	3	102	Wrong
4	4	100
4	5	103
5	1	102
6	1	101

Thanks in advance,
Janu

 

[Joe4]

I am not sure I follow your logic/example.
If the following is really true:

for one room in one house, if the code is 100 or 101, then the other rooms in that house should not have a code 102.

then shouldn't ALL of the entries for House Number 4 be marked as "Wrong" instead of just a few?

 

[janu319]

I am not sure I follow your logic/example.
If the following is really true:

then shouldn't ALL of the entries for House Number 4 be marked as "Wrong" instead of just a few?

Thanks for your reply.
As the room 2 in House number 4 has code 101, the other rooms in house number 4 should not have code 102, any other codes like 100 or 103 are possible.

 

[Joe4]

OK, I see now.

Put this formula in cell D2 and copy down for all rows:

=IF(AND(C2=102,OR(COUNTIFS(A:A,A2,C:C,100)>0,COUNTIFS(A:A,A2,C:C,101)>0)),"Wrong","")

 

[janu319]

Thank you so much.

 

[Joe4]

You are welcome.