Solved

Excel 2010 Formula to compare first two characters of each column

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

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Export PDF Form fields to Access  or Excel  in Tab order 16 129
How to count Capital Letters in a cell? 1 37
count values within multiple bands 7 35
sort time order 10 46
Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

710 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