conditional formatting duplicate values two conditions

[mohamed ali abdelgawad]

Hi Dears
need your help here please

i have 3 columns

in column A i enter ( In or Out )
in column B and C i enter numbers

what i want to do to highlight duplicated value only if thecell A value = IN
two conditions ( duplicated and cell A = IN )
attached sheet will help you to understand what i want exactly

Download

hope my weak English help me to delevere what i want

 

[estevaoba]

Hi, Mohamed!

I use helper columns (H and I) to list numbers when column A is "IN" only. Those helper columns can be hidden.
Formula below in H2, copy to I2, then copy down:

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=IF($A2="IN",B2,"")[/TD]
[/TR]
</tbody>[/TABLE]

Next, select your data range for highlighting.
In conditional formatting, select New Rule,
Then select "Use a Formula to determine cells to format",
Paste formula below in "Format Values in which this formula is true"
Select format pattern as desired, click OK.

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=AND($A2="IN",OR(COUNTIF($H$2:$I$100,H2)>1,COUNTIF($H$2:$I$100,I2)>1))[/TD]
[/TR]
</tbody>[/TABLE]

Adjust range in formula to fit.

Good luck!

 

[inactiveuserps07]

Hello, Mohamed. Here is another solution. You will need to apply TWO conditional formatting rules:

Select the range from B2 down. Apply the following custom CF rule and format the fill as desired:

=AND(UPPER(A2)="IN",SUMPRODUCT((UPPER(A:A)="IN")*((B:B=B2)+(C:C=B2)))>1)

Select the range from C2 down. Apply the following custom CF rule and format the fill as desired:

=AND(UPPER(A2)="IN",SUMPRODUCT((UPPER(A:A)="IN")*((B:B=C2)+(C:C=C2)))>1)