Alternate Row color over groups using conditional formatting

[jrobin5881]

In Excel 2003 I have my data sorted so that it groups by row in column A. For instance A1= Smith, A2 = Smith, A3=Smith and then A4= Jones,A5 =Jones, etc etc. I want to shade the rows based on the grouping in the A column. In an empty column to the right in Column P i put a formula in that reads: =IF(OFFSET(A2,1,0)<>A2,1,) So that gave me groups of 1's and 0's. I tried doing conditional formatting that said If P2=1, then shade yellow. I added a second condition that said if P2=0 and made that color green. I'm expecting it shade the records one color or the other based on the value in the P column but that's not working. Any suggestions?

 

[AnotherHiggins]

FYI: You don't need to use [Ctrl] + [Shift] + [Enter] when entering the formula in the Conditional Formatting box.

Just to clarify:

In a spare cell use a formula like =A1=A2. That will return True or False.

Well I would suggest:
Copy the previous cell to the one in question. Then you KNOW you have the same values and conditional formats.

You did these things in the ORIGINAL data, right? Not the sheet with the index match functions, right?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

"Skip's solution"

WRONG! It is your APPLICATION of my solution.

Seems as if you are having a problem comprehending ANY & ALL of our suggestions. They ALL work!

My approch has a formula that returns a valuve in a helper column. The advantage for you is that you can SEE, with your own two eyes, what that value is. The value that should be returned is...
[tt]
=if(a2=a1,z1,z1+1)
A Z =mod($z2,2)=0
West 1 FALSE
West 1 FALSE
West 1 FALSE
East 2 TRUE
East 2 TRUE
North 3 FALSE
North 3 FALSE
North 3 FALSE
North 3 FALSE
North 3 FALSE
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}