• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

Excel compare 2 cells...

This is so unbelievable in comparing 2 cells not working.  I have tried =A1=O1, EXACTMATCH, COUNTBLANK and a number of other IF statements and none work.

It is super simple what I am trying to do.

Compare 2 cells that may or may not have null values.  If the text is identical, give me a "O" else give me an "X".

So if the cells are both null or both exact text matches I want a "O", otherwise an "X".

PLEASE HELP lol....I have been doing this for hours and nothing is working (shouldn't this be easy?)  :)
0
cyimxtck
Asked:
cyimxtck
1 Solution
 
oleggoldCommented:
i'd try search, also You may have blanks so You'll need to trim the values in both cells.
0
 
pony10usCommented:
=IF(A1=O1,"O","X")
0
 
cyimxtckAuthor Commented:
=IF(A1=O1,"O","X") doesn't work.  It evaluates to O for all values regardless if they are different or not.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
cyimxtckAuthor Commented:
null and text is a case in point.  A1 = null and O1 has text
0
 
NorieVBA ExpertCommented:
Strange if I try this with A1 empty (null) and O1 with text the result is X.

=IF(A1=O1, "O", "X")

What exactly do you have in A1 and O1?
0
 
pony10usCommented:
Have I missed something in your request?  This is what I get as a result:

eecaptuer.jpg
Using Excel 2007, formula in C1 and copied down through C6
0
 
pony10usCommented:
If I put a space (blank character) in A7 and nothing in cell B7 then I get an X because they don't equal even though they look like they do.  That is the only option that I can think of that would cause this.
0
 
cyimxtckAuthor Commented:
MS Office Professional Plus 2010 here is what I get:

IF(A200=O200, "O", "X")

A200 = nothing
O200 = Interactive Data

X works and finds the correct answer.

IF(A203=O203, "O", "X")

Fails and gives me an X when both cells are blank.

IF(TRIM(A201)=TRIM(O201), "O", "X")

A201 = nothing
O201 = Spotfire

Fails and gives me a O.

See what I mean? this should be so simple but things are not working.  Is there a setting somewhere or something?
0
 
cyimxtckAuthor Commented:
Something is jacked with that other spreadsheet....I did the same test on my laptop and your above logic works....NO clue how that is possible but I have been pulling out what little hair I have left for no reason.

Maybe it is corrupt?

Thanks for the help and sanity test!
0
 
pony10usCommented:
Thanks for letting us know.  I was starting to pull out what's left of my own hair.  I couldn't get it to fail and I have been trying all sorts of ways.  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now