Failed Comparison... why?

[shelfish]

Worksheet one....

Cell A1 contains formula: if(B1 = Sheet2!A1), "Yeah!", "Damn!")

Cell B1 contains a concatenation of C1 and D1, a model number and
serial number (ex. 141565). This number was generate by a macro, so
there is no formula in the cell, just the value.

Worksheet two is a database export and A1 DOES contain the same value.
I have verified there are not any leading or trailing spaces and made
sure that the cell format was the same for both...text.

But the formula returns false for some reason.

If I retype the value generated by the macro and hit enter, then it
will return true. If I replace the macro-generated value with a
concatenate formula, it also seems to work just fine. But this is not
an option for me due to the amount of IF statements needed to
correctly pair the model/serial numbers so that they match the values
in sheet 2

Any thoughts on what Excel quirk would cause it to see these two
values as unequal?

Many thanks,
S.

 

[Wigi]

Not sure if that's the solution, but there is an extra ) in your formula.

 

[shelfish]

No. I just gave a simple example of a very complex situation. Sorry
for the typo. I'm really not a newb, but I have yet to find anyone I
work with who can figure it out either. I am hoping someone in here is
up for the challenge...

Put a different way, can anyone name a reason why two cells, which are
identical in every way that I can detect, would not be considered
equal by Excel?

I know about 4 experts who would be really impressed by your answer.
And, of course, I would really appreciate finding a solution.

Thanks,
S.

 

[JLGWhiz]

Try using a message box to see what each returned value looks like. If one
has quote marks and the other doesn't it means they are not both text.
Sub ckType()
MsgBox ActiveSheet.Range("A1").Value & " " & _
Sheets("Sheet2").Range("A1").Value 'all one line
End Sub

Also try making the comparison using the Trim function to see if there are
leading and trailing spaces that are causing the problem. IF(TRIM(B1) =
TRIM(Sheet2!A1), "OK", "OS")

 

[JLGWhiz]

I misread your explanation and use A1 for the macro comparison. Should be:

Sub ckType()
MsgBox ActiveSheet.Range("B1").Value & " " & _
Sheets("Sheet2").Range("A1").Value 'all one line
End Sub

 

[Tim Williams]

Can you make the (minimal) workbook available ?
E-mail is OK: tim j williams at gmail dot com (no spaces, etc)
Tim

 

[Ron Rosenfeld]

Any thoughts on what Excel quirk would cause it to see these two
values as unequal?

I've not seen an instance where two values that are truly equal get evaluated
as unequal.

Since your content contains both numbers and letters, the cell format should be
irrelevant.

I would try to track down the difference by doing such things as checking the
LENgth of the cell contents; and then doing a character by character analysis
to see what is going on, exactly.

You can extract the characters, one by one, by using a formula:

e.g.

=MID($A$1,ROWS($1:1),1)

will return the first character in A1. If you fill down this formula, it will
adjust to return the 2nd, third, etc characters.

=CODE(MID($A$1,ROWS($1:1),1))

will return the ASCII code for that character, and may help pick out
non-printing characters.

Do the same for your two cells, side by side, and you should be able to
determine the problem.
--ron

 

[shelfish]

I misread your explanation and use A1 for the macro comparison. Should be:

Sub ckType()
MsgBox ActiveSheet.Range("B1").Value & " " & _
Sheets("Sheet2").Range("A1").Value 'all one line
End Sub

Right. I got what you meant. Msgbox had both values with no additional
marks on either. So I assume that means they are both viewed as
numbers. I also plugged in the trim function as you mentioned and it
returned true ("OK").

But again, in the cell directly below that, I entered =IF(B2='sheet 2'!
A2,"OK","OS") and got false ("OS").

Wow.

 

[shelfish]

Can you make the (minimal) workbook available ?
E-mail is OK: tim j williams at gmail dot com (no spaces, etc)
Tim

....Done!

 

[shelfish]

I've not seen an instance where two values that are truly equal get evaluated
as unequal.

Since your content contains both numbers and letters, the cell format should be
irrelevant.

I would try to track down the difference by doing such things as checking the
LENgth of the cell contents; and then doing a character by character analysis
to see what is going on, exactly.

You can extract the characters, one by one, by using a formula:

e.g.

=MID($A$1,ROWS($1:1),1)

will return the first character in A1. If you fill down this formula, it will
adjust to return the 2nd, third, etc characters.

=CODE(MID($A$1,ROWS($1:1),1))

will return the ASCII code for that character, and may help pick out
non-printing characters.

Do the same for your two cells, side by side, and you should be able to
determine the problem.
--ron

Excellent sugg. I never would have thought of that. But I got
identical chars all the way down.

 

[Mark Lincoln]

You mentioned that both cells in question are formatted as Text, but
when I have two absolutely identical numbers that don't evaluate as
being equal it's always a case one cell formatted as Text versus one
that is not. Does the database import manage to change the formatting
of that cell? Or is Excel somehow treating it as a number regardless
of the formatting?

Mark Lincoln

 

[shelfish]

You mentioned that both cells in question are formatted as Text, but
when I have two absolutely identical numbers that don't evaluate as
being equal it's always a case one cell formatted as Text versus one
that is not. Does the database import manage to change the formatting
of that cell? Or is Excel somehow treating it as a number regardless
of the formatting?

I thought that might be it so I formatted them both as text. It seemed
to work because the alignment immediately changed. But they still did
not evaluate as equal.

Thanks,
S.

 

[Ron Rosenfeld]

Excellent sugg. I never would have thought of that. But I got
identical chars all the way down.

Hmm

Could you post the macro you are using along with some examples of the data
that is not comparing as equal. Perhaps if you could just do a copy the data
from your worksheet and paste it into the NG message.
--ron

 

[Mark Lincoln]

This is certainly a curious thing. Try this:

=IF(VALUE(B1) = VALUE(Sheet2!A1)), "Yeah!", "Damn!")

It worked for me when I entered a value (12345) as text in one cell
and as a number in another. If it doesn't work in your situation,
we're back to wondering what is different about the two entries.

Mark Lincoln

 

[Mark Lincoln]

I forgot to ask: How is your macro code generating the contents of
B1? Cell B1 looks to be the prime suspect since you can re-enter the
same figure into it to make the formula work. That leads to the macro
code. I would at least like to see the concatenation portion of the
code, the values (variables) it is using, and where those values come
from.

Mark Lincoln

 

[shelfish]

I forgot to ask: How is your macro code generating the contents of
B1? Cell B1 looks to be the prime suspect since you can re-enter the
same figure into it to make the formula work. That leads to the macro
code. I would at least like to see the concatenation portion of the
code, the values (variables) it is using, and where those values come
from.

I did a lot of copy and pasting so ignore minor syntax errors...it
works just fine except for stated problem.

Here ya go...

SET VALUES
Range("d2").Select
Serial = ActiveCell.Value
Range("c2").Select
Model = ActiveCell.Value

'NO BLANKS... "" MEANS EOF
Do
If ActiveCell.Value = "" Then
Exit Do
Else
'LOTS OF IF/ELSE STATEMENTS OMITTED.
If Model = "182T" Then
ActiveCell.Offset(0, -1).Activate
ActiveCell.Value = "182" & Serial
ActiveCell.Offset(1, 2).Activate
Serial = ActiveCell.Value
ActiveCell.Offset(0, -1).Activate
Model = ActiveCell.Value
End if
Loop
Range("a2").Select
With Worksheets("sheet1l")
Set y = .Range("A2", _
.Cells(.Range("D2").End(xlDown).Row, 1))
End With
Range("a2").Select
'UNIT IS A NAMED RANGE ON SHEET 1
ActiveCell.Formula = "=IF(ISERROR(MATCH(B2,unit,0)),""x"",B2)"
Selection.AutoFill Destination:=y
ActiveCell.Value = "182" & Serial
ActiveCell.Offset(1, 2).Activate
Serial = ActiveCell.Value
ActiveCell.Offset(0, -1).Activate
Model = ActiveCell.Value
'DELETE ALL "X"s (UNITS NOT IN COMMON)

 

[Ron Rosenfeld]

I thought that might be it so I formatted them both as text. It seemed
to work because the alignment immediately changed. But they still did
not evaluate as equal.

Thanks,

Changing the format of the cell does NOT necessarily change how Excel views it.

But since you write the characters are the same and it appears that, contrary
to my original guess, the values are all numbers, then I suspect that the
problem is that one is TEXT and the other General or Number.

Changing the format AFTER the cell has been filled with data will NOT change
how Excel views that cell.

So try this and see what you get:

=ISTEXT(Sheet2!A1)
=ISTEXT(B1)


--ron

 

[Mark Lincoln]

Nothing leaps out at me and announces it's a problem.

I agree with Ron--one of your values must be formatted as Text and the
other as a number, and as Ron pointed out, changing the formatting
afterward doesn't necessarily work as you'd think. If you can't fix
the formatting for some reason, I still think you can test for
equality as I posted earlier:

=IF(VALUE(B1)=VALUE(Sheet2!A1),"Yeah!","Damn!")

If each cell contains exactly the same digits, and there are only
digits, this should work. The formatting of the two cells won't be an
issue.

Mark Lincoln

 

[Ron Rosenfeld]

I did a lot of copy and pasting so ignore minor syntax errors...it
works just fine except for stated problem.

Here ya go...

SET VALUES
Range("d2").Select
Serial = ActiveCell.Value
Range("c2").Select
Model = ActiveCell.Value

'NO BLANKS... "" MEANS EOF
Do
If ActiveCell.Value = "" Then
Exit Do
Else
'LOTS OF IF/ELSE STATEMENTS OMITTED.
If Model = "182T" Then
ActiveCell.Offset(0, -1).Activate
ActiveCell.Value = "182" & Serial
ActiveCell.Offset(1, 2).Activate
Serial = ActiveCell.Value
ActiveCell.Offset(0, -1).Activate
Model = ActiveCell.Value
End if
Loop
Range("a2").Select
With Worksheets("sheet1l")
Set y = .Range("A2", _
.Cells(.Range("D2").End(xlDown).Row, 1))
End With
Range("a2").Select
'UNIT IS A NAMED RANGE ON SHEET 1
ActiveCell.Formula = "=IF(ISERROR(MATCH(B2,unit,0)),""x"",B2)"
Selection.AutoFill Destination:=y
ActiveCell.Value = "182" & Serial
ActiveCell.Offset(1, 2).Activate
Serial = ActiveCell.Value
ActiveCell.Offset(0, -1).Activate
Model = ActiveCell.Value
'DELETE ALL "X"s (UNITS NOT IN COMMON)

One other comment about your code. There is no need at all to activate or
select a cell before either reading or writing a value into that cell (or doing
any other cell action, for that matter).

The omission would make your code more readable.

For example,

SET VALUES
Range("d2").Select
Serial = ActiveCell.Value
Range("c2").Select
Model = ActiveCell.Value

and

SET VALUES
Serial = Range("d2").Value
Model = Range("c2").Value

are equivalent.
--ron

 

[Ron Rosenfeld]

I did a lot of copy and pasting so ignore minor syntax errors...it
works just fine except for stated problem.

Here ya go...

SET VALUES
Range("d2").Select
Serial = ActiveCell.Value
Range("c2").Select
Model = ActiveCell.Value

'NO BLANKS... "" MEANS EOF
Do
If ActiveCell.Value = "" Then
Exit Do
Else
'LOTS OF IF/ELSE STATEMENTS OMITTED.
If Model = "182T" Then
ActiveCell.Offset(0, -1).Activate
ActiveCell.Value = "182" & Serial
ActiveCell.Offset(1, 2).Activate
Serial = ActiveCell.Value
ActiveCell.Offset(0, -1).Activate
Model = ActiveCell.Value
End if
Loop
Range("a2").Select
With Worksheets("sheet1l")
Set y = .Range("A2", _
.Cells(.Range("D2").End(xlDown).Row, 1))
End With
Range("a2").Select
'UNIT IS A NAMED RANGE ON SHEET 1
ActiveCell.Formula = "=IF(ISERROR(MATCH(B2,unit,0)),""x"",B2)"
Selection.AutoFill Destination:=y
ActiveCell.Value = "182" & Serial
ActiveCell.Offset(1, 2).Activate
Serial = ActiveCell.Value
ActiveCell.Offset(0, -1).Activate
Model = ActiveCell.Value
'DELETE ALL "X"s (UNITS NOT IN COMMON)

One other comment about your code. There is no need at all to activate or
select a cell before either reading or writing a value into that cell (or doing
any other cell action, for that matter).

The omission would make your code more readable.

For example,

SET VALUES
Range("d2").Select
Serial = ActiveCell.Value
Range("c2").Select
Model = ActiveCell.Value

and

SET VALUES
Serial = Range("d2").Value
Model = Range("c2").Value

are equivalent.

<Hit SEND too soon>

Equivalent except for the position of the cursor on the worksheet when you are
done.
--ron