conditional format to ensure data range matches

**tuizner** · 03-14-2010, 08:26 PM

Hi

Need a Conditional Format or Validation formula to highlight when, for the same value in column A (12) the values in column B are NOT the same (should be all 46 or all 47).

Order# Customer#
12 ____ 46
12 ____ 47
12 ____ 47

Thanks

Mat

**DonkeyOte** · 03-15-2010, 09:14 AM

Which version of XL are you using ?

Assuming Pre XL2007 and cells to be validated are B2:B4 then with said range highlighted:

=SUMPRODUCT(($A$2:$A$4=$A2)*ISNUMBER($B$2:$B$4)*($B$2:$B$4<>$B2))=0

Validation has it's limits of course...

**tuizner** · 03-15-2010, 12:20 PM

Originally Posted by DonkeyOte

Which version of XL are you using ?

Assuming Pre XL2007 and cells to be validated are B2:B4 then with said range highlighted:

=SUMPRODUCT(($A$2:$A$4=$A2)*ISNUMBER($B$2:$B$4)*($B$2:$B$4<>$B2))=0

Validation has it's limits of course...

Thanks Senor Quixote - great work!

Works a treat - can also increase range A2:A999 for example.

One error though and that is your final term should be <>0 rather than =0
=SUMPRODUCT(($A$2:$A$4=$A2)*ISNUMBER($B$2:$B$4)*($B$2:$B$4<>$B2))=0

**DonkeyOte** · 03-16-2010, 06:41 AM

Originally Posted by tuizner

One error though and that is your final term should be <>0 rather than =0

Yes if you're referring to Conditional Formatting.

Not so if you're referring (as I was) to Data Validation (the =0 test ensures entry is consistent with prior entries should they exist)