Excel 2010 Formula to compare first two characters of each column
Posted on 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.