Excel 2010 compare two columns with multiple conditions

Hi Experts,

I would like to compare the two columns and highlighed the row if the specific condition is met like below:

Comparison 1:

- compare the strings in column A and B whether strings in the columns are capitalized or not.  E.g.,  

Column A           Column B
 SMITH           smith

Note: I would like to compare purely the strings if it's capitalized or not. I'm not comparing if the strings matches or not.

Comparison 2:
 - compare column A and B and if column A contains specific strings and column B has specific set of strings, highlight the row.

E.g.,
If the column A contains the string, "Open" and column B contains either "In Queue" or "In Test", or "In Progress", then, highlight the row


Thanks!
SFtotoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NBVCCommented:
I am confused about 1st condition.

Do want to highlight only if one is capitalized and the other isn't, or do you want to highlight as long as there is capitilization in either or both columns?
0
SFtotoAuthor Commented:
I want to highlight only if one is capitalized and the other isn't.

Thanks!
0
NBVCCommented:
Sorry, this got lost in my inbox....  are you still requiring assistance?
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

SFtotoAuthor Commented:
Yes. It would be great if someone can show me how to do it.
0
NBVCCommented:
Assuming columns A and B, then select those columns:

Go to Home|Conditional Formatting|New Rule, then select:  use a formula to determine which cells to format

Then enter formula:

=AND($A1<>"",$B1<>"",OR(AND(EXACT($A1,UPPER($A1)),EXACT($B1,LOWER($B1))),AND(EXACT($A1,LOWER($A1)),EXACT($B1,UPPER($B1)))))

where A1 and B1 are top most cells.

click Format and choose colour.

click ok, then click New rule and repeat as above with formula:

=AND($A1="Open",OR($B1="In Queue",$B1="In Test",$B1="Progress"))

click Format and choose colour.

Click Ok.

Now in the rules list, make sure that last rule is at the top, and check the box that says Stop if True.

Let me know how it works.... and sorry again for losing track of your thread.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SFtotoAuthor Commented:
Thanks! This worked!!
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.