Conditional Formatting in Excel

Closed
Zack - Apr 14, 2010 at 12:24 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 14, 2010 at 02:19 PM
Below is a small sample of the data that I am working with. Each column represents a year and each row represents an account number. What I would like to do is highlight the account numbers that are unique in comparison to the prior year and that year alone. For instance for year 2 I want to know what new accounts have been created in respect to year 1 and highlight the new account numbers in year 2 (ie. account numbers 20178 and 20792). I want to do this for each subsequent year without alterating the formatting for the prior year. I am aware of conditional formatting, however, it is also highlighting for the previous year in respect to accounts that are no longer in existence. For instance in year 1 account number 83681 was deleted so the conditional formatting I am using now will also highlight 83681 and again I am only interested in highlighting new accounts. Please advise thank you greatly.

Year 1 Year 2 Year 3 Year 4
29479 29479 29479 29479
20188 20178 20178 20178
20297 20297 20297 20597
20792 20796 20796 20796
83681 00000 85251 85251

3 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 14, 2010 at 01:16 PM
How are you applying cond. format

Try this

based on you sample

select range B2:D6

goto conditional format and apply this formula

=ISERROR(MATCH(B2,A:A,0))

It looks at a cell and then see if that value exist to one column left to it

like value found in B2 if not in column A:A will be highlighted
0
rizvisa1 I have to admit my ignorance in regards to excel. I selected B2 through B24 and each subsequent column until coumn F2 through F24 to select the entire data range without headers. I then typed the formula you gave me into the fx box and nothing happened. I know I have done something wrong can you please advise further.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 14, 2010 at 02:19 PM
Zack, based on above, it seems to me that your data is between range A2:F24

So what i was trying to say was
Select you range (leaving out the starting point which would be A2:A24)
Then go to conditional Format
And paste the formula there. Then from the conditional format, click on format and choose the color of your choice
0