Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2618
  • Last Modified:

Excel 2010 Formula to compare first two characters of each column

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
RNikolai001
Asked:
RNikolai001
1 Solution
 
Rgonzo1971Commented:
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
 
RNikolai001Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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