• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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