# Find the wrong value

#### janu319

##### New Member
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

Janu

#### Attachments

• codes.PNG
12.8 KB · Views: 5

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Joe4

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

##### New Member
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?
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:
Excel Formula:
``=IF(AND(C2=102,OR(COUNTIFS(A:A,A2,C:C,100)>0,COUNTIFS(A:A,A2,C:C,101)>0)),"Wrong","")``

#### janu319

##### New Member
Thank you so much.

You are welcome.

Replies
16
Views
375
Replies
3
Views
48
Replies
6
Views
93
Replies
5
Views
102
Replies
1
Views
128

1,129,573
Messages
5,637,135
Members
416,959
Latest member
Mohzein

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back