comparing two spreadsheets

[Janis]

I just want to compare two spreadsheets with text and numbers in a 3rd
spreadsheet:

I have the formula in every cell on the 3rd spreadsheet:

IF('my spread sheet1'!a1='my spreadsheet2'a1)true,False)

The problem is some values are text and some are numbers on the two sheets.
I am getting some false positives. So is the problem that it can't compare
text only numbers or what?

So if I add value:
If(value('my spreadsheet1'!a1)=value('my spreadsheet2'a1))true,False)
that gives me a #value error.

Value would only work on a text field right? So it is possible that I need
to do nested ifs?

I have another problem on one column I have a number field where the
matching cell in the other column is the same number but I still get a false
with this formula. I formatted the column on both spreadsheets as a number
and the formula works. The problem is the spreadsheets come from someone
else as all text. I I cannot change the comparison spreadsheets. So I have
to convert to

This is a report spreadsheet and there are also some blanks. This also
causes a false negative or positive.



thanks,

 

[Dave Peterson]

Maybe instead of converting to numbers, you could do the compares as text:

=if(sheet1!a1&""=sheet2!a1&"",true,false)

or just
=sheet1!a1&""=sheet2!a1&""

Concatenating an empty string ("") won't change any strings and will force
numbers to text.

 

[Janis]

It worked you get a gold star. Thanks,

Maybe instead of converting to numbers, you could do the compares as text:

=if(sheet1!a1&""=sheet2!a1&"",true,false)

or just
=sheet1!a1&""=sheet2!a1&""

Concatenating an empty string ("") won't change any strings and will force
numbers to text.