Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
Not sure if that's the solution, but there is an extra ) in your formula.
-- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "shelfish" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
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") "shelfish" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
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 "shelfish" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
Can you make the (minimal) workbook available ?
E-mail is OK: tim j williams at gmail dot com (no spaces, etc) Tim "shelfish" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
On Tue, 4 Dec 2007 12:13:53 -0800 (PST), shelfish wrote:
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
On Dec 4, 7:45 pm, JLGWhiz wrote:
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
On Dec 4, 9:47 pm, "Tim Williams" <timjwilliams at gmail dot com
wrote: Can you make the (minimal) workbook available ? E-mail is OK: tim j williams at gmail dot com (no spaces, etc) Tim "shelfish" wrote in message ... ....Done! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
On Dec 5, 5:55 am, Ron Rosenfeld wrote:
On Tue, 4 Dec 2007 12:13:53 -0800 (PST), shelfish wrote: 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 Excellent sugg. I never would have thought of that. But I got identical chars all the way down. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
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 On Dec 4, 3:13 pm, shelfish wrote: 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
On Dec 5, 12:15 pm, Mark Lincoln wrote:
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
On Wed, 5 Dec 2007 06:49:54 -0800 (PST), shelfish wrote:
On Dec 5, 5:55 am, Ron Rosenfeld wrote: On Tue, 4 Dec 2007 12:13:53 -0800 (PST), shelfish wrote: 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 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
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 On Dec 5, 3:41 pm, shelfish wrote: On Dec 5, 12:15 pm, Mark Lincoln wrote: 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. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
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 On Dec 5, 3:41 pm, shelfish wrote: On Dec 5, 12:15 pm, Mark Lincoln wrote: 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. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
On Dec 5, 3:14 pm, Mark Lincoln wrote:
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) |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
On Wed, 5 Dec 2007 12:41:09 -0800 (PST), shelfish wrote:
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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
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 On Dec 5, 5:13 pm, shelfish wrote: On Dec 5, 3:14 pm, Mark Lincoln wrote: 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) |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
On Wed, 5 Dec 2007 14:13:52 -0800 (PST), shelfish wrote:
On Dec 5, 3:14 pm, Mark Lincoln wrote: 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) 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 |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
On Wed, 5 Dec 2007 14:13:52 -0800 (PST), shelfish wrote:
On Dec 5, 3:14 pm, Mark Lincoln wrote: 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) 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 |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
On Dec 5, 5:43 pm, Ron Rosenfeld wrote:
On Wed, 5 Dec 2007 12:41:09 -0800 (PST), shelfish wrote: 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 istext didn't seem to work but I went back the the TRIM function mentioned above and that seems to do it. I worked it into the MATCH formula in column B. On another note, I also worked into the macro RANGE('C:C").NUMBERFORMAT = "@" It had no effect. Thanks for all the help....all. S. |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Failed Comparison... why?
On Thu, 6 Dec 2007 07:10:03 -0800 (PST), shelfish wrote:
istext didn't seem to work but I went back the the TRIM function mentioned above and that seems to do it. I worked it into the MATCH formula in column B. That's not very helpful. I don't know what "istext didn't seem to work" means. Formula didn't calculate? If so, then you entered it as text and/or you have calculations set to manual. Formula returned an error message, rather than TRUE or FALSE? If so, what is the error message? --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
row comparison | Excel Worksheet Functions | |||
Complex comparison of Columns of Data: Extracting unique records after comparison on 4 levels | Excel Programming | |||
comparison | Excel Worksheet Functions | |||
Comparison | Excel Worksheet Functions | |||
Comparison | Excel Programming |