1

Related to this question, I am trying to compare a table of settings to identify some configuration issues. I am using the following formula to highlight differences between two known good configs (Columns B and C) and one that isn't working (Column D).

=OR($B2<>$D2,$C2<>$D2)

enter image description here

The problem I'm having is that values of False, 0 and <blank> all "match" according to Excel.

How can I adjust my formula to highlight these rows?

THE JOATMON
  • 1,878
  • 9
  • 49
  • 81
  • You could possibly just compare the length of the two strings. Otherwise you have to expand your formula to exclude those values using IsBlank etc – Brad Patton Sep 12 '19 at 13:58
  • Provide some sample data, please. Or you may try to concat some constant char to the value - this will convert the value to text type, including empty cells values, something like `=OR($B2&"@"<>$D2&"@",$C2&"@"<>$D2&"@")`. – Akina Sep 12 '19 at 13:58

2 Answers2

1

Forcing them to evaluate as strings seems to result in the desired behavior.

=OR($B2&""<>$D2&"",$C2&""<>$D2&"")
THE JOATMON
  • 1,878
  • 9
  • 49
  • 81
1

Alternatively you can use COUNTIF, it'll differentiate those values properly:

=COUNTIF($B2:$C2,$D2)<2

enter image description here

Máté Juhász
  • 21,403
  • 6
  • 54
  • 73