Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel 2010 Formula to compare first two characters of each column

Posted on 2014-03-20
2
Medium Priority
?
1,883 Views
Last Modified: 2014-03-20
When I first created a conditional formatting rule (had 1500 rows of data), the formula worked for the specified column. Now that my  cumulative list is growing (have 2100 rows of data), my formula only works on certain cells in the specified column. The formula has a larger range than I need so that the list can grow without the need to revise the formula. I have ten other conditional formatting formulas on different columns that work without failure and none have the box "Stop if True" checked.

I wish to highlight column B cells where the first two left characters are not equal to the first two left characters of column A.
I am using this formula in a conditional formating formula field "=NOT(OR(LEFT($a2,2)=LEFT(b$2:b$2997,2)))" but it will not work for all cells in the column. I have used "formatting rules for this worksheet," but the formula does not appear to apply to all cells in column B.


First column text "Alien", second column text "Aliens" - formula should return no conditional formatting.
First column text "Alien", second column text "Marketing" - formula should return conditional formatting.

Some cells in column B are reflecting the correct conditional formatting. In those cells if I revise the text in column A, the end result is just what I expect so I feel like the formula is correct. It's appears to apply to only a few scattered cells in the column B.
0
Comment
Question by:RNikolai001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39942349
Hi,

pls try if you want to compare row per row

=LEFT($a$2,2)<>LEFT($b2,2)

Open in new window

Regards
EDIT

File inserted
EE20140320.xlsx
0
 

Author Closing Comment

by:RNikolai001
ID: 39942755
Thank you very much for your quick response.  I removed one of the dollar signs (after "a") and it worked perfectly. =LEFT($a2,2)<>LEFT($b2,2)  Your formula was so much cleaner than mine.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question