Highlight duplicate rows based on 8 conditions (with one catch)

[ole123]

Hi

I have a dataset with 8 column, i.e. 8 conditions, for which I would like to highlight the rows that are duplicate with all 8 conditions.

There is one catch, however, one of the columns should ignore + and -, and thus should consider only if the absolute values are duplicates.

I have attached a sample file, which clearly explains what I am after.

I appreciate any inputs

/O

 

[Nebu]

Hi:

Use this formula in conditional formatting.

=COUNT(IF(MMULT(($A$7:$A$12=$A7)*($B$7:$B$12=$B7)*($C$7:$C$12=$C7)*($D$7:$D$12=$D7)*(ABS($E$7:$E$12)=ABS($E7))*($F$7:$F$12=$F7)*($G$7:$G$12=$G7)*($H$7:$H$12=$H7),1),$A$7:$A$12))>1

Thanks

 

[NARAYANK991]

Hi ,

Another possibility :

=IF(OR(SUMPRODUCT(($B$7:$B$12 = $B7) * ($E$7:$E$12)) = 0, SUMPRODUCT(($B$7:$B$12 =$B7) * ($E$7:$E$12)) = 2 * $E7), 1, 0)

See the attached file.

Narayan