Solved

Excel 2010 Formula to compare first two characters of each column

Posted on 2014-03-20
2
1,649 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 500 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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

631 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