Conditional Formatting Question

M

markstro

I have copied data from two different databases into a workbook.
I need to verify that data matches between the two sets of data and I
am using conditional formatting.
I have formatted a column between to sets of data to turn green if
they match.
My formula is =IF(a1=b1,true,false)
My problem is that the formula comes up true when one column has a
blank in it.
No way it equals the other. I have tried different formats; number,
general, text etc, with no joy. I cannot determine how the blank cell
can possibly equal the other cell. The formula works for 90% of the
data, it's the 10% I cannot figure out.
Thanks for any help, open to questions for more information to figure
this out.
 
B

Bob Phillips

Markstro,

You only need a formula of

=A1=B1

But it doesn't give TRUE unless A1 and B1 are both blank

Do you have formulas in either cell?

ARe you sure that one doesn't have spaces? Do a =LEN(A1) and =LEN(B1) and
see if one is not zero.
 
M

markstro

Thanks Bob, I need to know that the two cells match instead of
scrolling down and visually comparing a couple hundred of cells.
I tried the =LEN and I get a #value error.
I tried the =a1=b1 in the conditional formula and got another error.
I cannot figure out why I cannot get these formulas to work.
I know there is something different about these cells, just cannot
find out what.
 
A

AlfD

Hi!

If there was really nothing in A1 then =len(A1) would return 0. That it
doesn't tells us something - not sure what, except that it isn't empty.

I suggest you use edit > clear all on that cell to see what happens.

All else failing, it is often quicker to reconstruct the worksheet.

Alf
 
M

markstro

AlfD said:
Hi!

If there was really nothing in A1 then =len(A1) would return 0. That it
doesn't tells us something - not sure what, except that it isn't empty.

I suggest you use edit > clear all on that cell to see what happens.

All else failing, it is often quicker to reconstruct the worksheet.

Alf
Click to expand...

But there is something in both cells, that is what I want to compare.
I want to see a mismatch.
What makes it return #value when I use =len(a1)?
I think that my problem comes from the formatting in the ASCII data
base that I have copied into this spreadsheet.
 
Top