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?

 

[SkipVought]

Hi,

In an adjacent column, use a formula like this, assuming that you data begins in row 2 (row 1 headings), and assuming that this is in column Z...
[tt]
=if(a2=a1,z1,z1+1)
[/tt]
now do the CF formula on the data in column Z...
[tt]
GREEN: =mod($z2,2)=0
YELLOW: =mod($z2,2)=1
[/tt]


Skip,

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

 

[jrobin5881]

Skip,

Thanks we're close. I had to modify the formula slightly to an absolute reference =IF(A2=A1,$Z$1,$Z$1+1).

Here's whats happening using your solution and my same example as a dataset. I have Smith in A2, A3, A4. The CF is shading A2 & A3 Yellow and then A4 (which is still Smith) is shading Green. In A5 I have Jones and the shading turns back to Yellow.

A4 should stay yellow and then A5, A6 & A7 where the name Jones is should be green and then it should go back to yellow when it sees a different value in A8

 

[AnotherHiggins]

You can do it without a helper column.
[ul][li]Select the columns that you want to conditionally format[/li]
[li]Go to Format > Conditional Formatting[/li]
[li]Change the first box to Formula Is[/li]
[li]Copy the following formula and paste into the next box:[/li]
[ul][li][COLOR=blue white]=Mod(Sum(1/CountIf($A$1:$A1,$A$1:$A1)),2)=1[/color][/li][/ul]
[li]Select the Format button[/li]
[li]Go to the Patterns tab and select the color you want[/li][/ul]


[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.

 

[jrobin5881]

anotherhiggins - It's not working that well. It's skipping some values and not shading others. I'm resolved to doing it manually I guess.

 

[AnotherHiggins]

Yikes. Don't do that. If it ever gets resorted, the colors would cease to make sense.

What about it isn't working? The statement "It's not working that well" is impossible to troubleshoot. It provides no detail about what you're seeing that is different than your expectations.

I've just set up an example sheet and the formula I provided is working fine. I have some values on a single row, others on two, three, four or more rows. It works across the board.

How about providing an example of your data (not just a description, but a table-format layout) and describe what you mean by Remeber - details are good.

Hint: You can wrap a table in [ignore][tt][/ignore] tags to ensure proper spacing.

Example:[ignore]
[tt]
Name Thing1[highlight]
Smith a
Smith b
Smith c[/highlight]
Jones a
Jones b[highlight]
Doe a[/highlight]
[/tt]
[/ignore]
would produce this:
[tt]
Name Thing1[highlight]
Smith a
Smith b
Smith c[/highlight]
Jones a
Jones b[highlight]
Doe a[/highlight]
[/tt]
Which is what I think you want to see.


[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.

 

[jrobin5881]

Another - Sorry my remark was just a sign of frustration...

My headings are actually begin in cell A5 as I want to add some drop down lists for my users to make selections. I did temporarily delete the rows and had the headings start in A1. The data I'm using is proprietary so I'm trying to re-enact with an example below
[tt]
Name Score1
1 - East 70
1 - East 70
[highlight]Q - West 75
Q - West 75
Q - West 75[/highlight]
3 - East 80
3 - East 80
3 - East 80
3 - East 80
3 - East 80
Hub - North 85
Hub - North 85
Hub - North 85
[highlight]Hub - North 85[/highlight]
Hub - North 85
[highlight]Hub - West 92
Hub - West 92
Hub - West 92
Hub - West 92
Hub - West 92
Hub - West 92[/highlight]
[/tt]
This was what I was trying to say it's not working well. It'll shade some groups and then skip or only do one line in others.

 

[AnotherHiggins]

I would bet that there is an extra space (or some kind of difference) between the Hub - North that is highlighted that the ones that aren't. Double click in that cell and, using the right and left arrow keys, look for an extra space.

[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.

 

[jrobin5881]

Higgins,
Row A is using a index match function to another spreadsheet that I have hidden. I went back to that sheet and made sure that all of the values were the same (no spaces). In looking what I posted before I had the random shading in the wrong spot. Below is exactly what I'm getting.

1st dataset no shading
2nd dataset shading
3rd dataset no shading
4th dataset shades last value or row in the group
5th dataset no shading
6th dataset shading


This may help a little more


[tt]
Name Score1
1 - East 70
1 - East 70
[highlight]Q - West 75
Q - West 75
Q - West 75[/highlight]
3 - East 80
3 - East 80
3 - East 80
3 - East 80
3 - East 80
Hub - North 85
Hub - North 85
Hub - North 85
Hub - North 85
[highlight]Hub - North 85[/highlight]
Hub - West 92
Hub - West 92
Hub - West 92
Hub - West 92
Hub - West 92
Hub - West 92
[highlight]2- East 95
2- East 95
2- East 95
2- East 95[/highlight]
[/tt]

 

[Gavona]

I would bet that there is an extra space ([red]or some kind of difference[/red]) between the Hub - North that is highlighted that the ones that aren't.

Before rejecting John's suggestion just test to see definitively if they are different. In a spare cell use a formula like =A1=A2. That will return True or False.


Gavin

 

[AnotherHiggins]

Okay, this is strange. So it doesn't change between the last "3 - East" and the first "Hub - North"?

I copied the data you just posted and pasted it into Excel - it still works fine for me.

I'm wondering if the cell references are set correctly.

To figure that out: What cell is the first piece of data in? (The cell below the header "Name"?) And if you single click on that cell, then go to Format > Conditional Formatting, what formula is displayed? Copy the formula and paste it here into Tek-Tips.

Also, what version of Excel are you running?

If Skip (or anyone else) is still following this, would you copy jrobin5881's data and see if the formula works for you?

[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.

 

[jrobin5881]

John, Gavona Thanks

First off - I did the =A1=A2 all the way down the column and everywhere it was false the color changed. EXCEPT Hub North 85 which evaluated to true yet the color changed?

John this is the IC in Cell A2 which is where my data starts. =MOD(SUM(1/COUNTIF($A$1:$A2,$A$1:$A2)),2)=1
Am running Microsoft Excel 2003 with SP2 installed.

I just want to add one other thing. I fooled around in a brand new sheet in a brand new work book and put a number in each cell beginging at A2 and something like this- 1112233344466666 and it did the same thing. Worked pretty good and then in the middle of the page turned one row an opposite color and not the rest in the group?

 

[GSCaupling]

jrobin: do you have any hidden rows? Subtotals, perhaps? Look before and after the Hub North 85 line that is highlighted.

Good luck.

 

[Gavona]

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

Also tell us the cell reference of the line in question and copy paste the conditional format formula in that cell to this thread. Also paste parts of the formula into a spare cell and see what they evaluate to. And check that the formats are set as for all the other cells - maybe just copy the formats from the cell above

However, whilst John's solution works for me, perfectly, I do not understand it. Pasted to a worksheet cell only the first item in each group returns True?????

[tt]Name Score1 A2=A1 MOD(SUM(1/COUNTIF($A$1:$A2,$A$1:$A2)),2)=1 SUM(1/COUNTIF($A$1:$A2,$A$1:$A2))
1 - East 70 FALSE TRUE 1
1 - East 70 TRUE FALSE 0.5
Q - West 75 FALSE TRUE 1
Q - West 75 TRUE FALSE 0.5
Q - West 75 TRUE FALSE 0.333333333
3 - East 80 FALSE TRUE 1
3 - East 80 TRUE FALSE 0.5
3 - East 80 TRUE FALSE 0.333333333
3 - East 80 TRUE FALSE 0.25
3 - East 80 TRUE FALSE 0.2
Hub - North 85 FALSE TRUE 1
Hub - North 85 TRUE FALSE 0.5
Hub - North 85 TRUE FALSE 0.333333333
Hub - North 85 TRUE FALSE 0.25
Hub - North 85 TRUE FALSE 0.2
Hub - West 92 FALSE TRUE 1
Hub - West 92 TRUE FALSE 0.5
Hub - West 92 TRUE FALSE 0.333333333
Hub - West 92 TRUE FALSE 0.25
Hub - West 92 TRUE FALSE 0.2
Hub - West 92 TRUE FALSE 0.166666667
2- East 95 FALSE TRUE 1
2- East 95 TRUE FALSE 0.5
2- East 95 TRUE FALSE 0.333333333
2- East 95 TRUE FALSE 0.25
[/tt]
I understand what the formula must be doing just don't understand how it is doing it!

Gavin

 

[Gavona]

As I said John's solution works for me. I am using Excel 2003 SP3

Gavin

 

[jrobin5881]

Thank you all for sticking with me on this issue.

- There are no hidden rows, columns or subtotals

- I copied and pasted the cell above to no avail. Remember I'm using an index match function in the A column. I even used format painter from the cell above to the Hub North row that is changing color and that did nothing.

- The cell in question is in cell A21 and the Conditional formula format for that cell reads: =MOD(SUM(1/COUNTIF($A$1:$A21,$A$1:$A21)),2)=1

 

[Gavona]

I would go back to Skip's formula (since I don't get John's).

In Z2: =if(a2=a1,z1,z1+1)
copied down

If those numbers change at the expected points then replace with:
=mod(if(a2=a1,z1,z1+1),2)



Gavin

 

[AnotherHiggins]

Mine is functioning as an Array Formula.

[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.

 

[Gavona]

Really, with no Ctrl-Shft-Enter automatically interpreted as array formula within Conditional Format... you live and learn but I have yet to master array formulae.

Gavin

 

[jrobin5881]

John & Gavin,

I took your advice and went back to skips advice. Here's the issue with this solution.

I'm only getting one line of highlighting at each change in grouping (see below). If I have West, West, West, East, East, North, North, North starting at A2 and going down the column then A2 highlights,A5 highlights and A7 highlights. What I want to happen is A2:A4 highligted and A7:A9 to highlight.

*** PS-I tried shift+control+enter in the Conditional Format dialog box enter an array but it would not work

Skips Solution
[tt]
[highlight]West[/highlight]
West
West
[highlight]East[/highlight]
East
[highlight]North[/highlight]
North
North
North
North
[/tt]

Desired Outcome
[tt]
[highlight]West[/highlight]
[highlight]West[/highlight]
[highlight]West[/highlight]
East
East
[highlight]North[/highlight]
[highlight]North[/highlight]
[highlight]North[/highlight]
[highlight]North[/highlight]
South
South
South
South

[/tt]