Conditional Formatting - color groups based on row value

[djangeroo]

Hi,
I am new to the forum and impressed by the great advise that's out there.

This is my problem:
A list with numbers in column A, these numbers can be used over multiple rows, sometimes 3, 5 or more/less.
Every time this number changes, the color for the entire row should flip between yellow and no color.
Sequence of the number in column A is not always odd / even. I tried various suggestions regarding this topic but I do not get it straight.

Thanks for your help.

 

[erik.van.geit]

Peter,

when you sort the data the conditional format stays OK
so this seems very useful ... and no helper-column: BRAVO !!

but, wouldn't this be "expensive" calculations, slowing down the sheet if there are many rows ?
I tried sorting 40.000 rows, just column A with data: the sheet needed 2 -3 minutes to update, while the "extra-column-method" was "fast" (3-4 seconds) (my PC is "rather old": I think 3,5 years)

kind regards,
Erik

 

[NBVC]

WOW, NBVC !!
nice to share this, thank you !!

I'm after this one since about 8 months, seems so easy when it's finished

the formula can be shortened to

=MOD(OFFSET($B2,-1,0) --($A2<>OFFSET($A2,-1,0)),2)

I wonder if we could find something without helper column

best regards,
Erik

No problem Erik,

That is what this forum is about! Helping each other to become better and more efficient with Excel. I am only to glad to share what I learn because this forum has taught me so much.

Thanks for the simplified version of the formula too!

 

[Peter_SSs]

Peter,

when you sort the data the conditional format stays OK
so this seems very useful ... and no helper-column: BRAVO !!

but, wouldn't this be "expensive" calculations, slowing down the sheet if there are many rows ?
I tried sorting 40.000 rows, just column A with data: the sheet needed 2 -3 minutes to update, while the "extra-column-method" was "fast" (3-4 seconds) (my PC is "rather old": I think 3,5 years)

kind regards,
Erik

Erik, when I first tested sorting with this formula I recollect getting some #REF! errors, but perhaps I did something wrong because it seems to be working OK now.

Yes, I imagine this formatting might slow things down in a big data range. However, your challenge was to achieve the result without helper columns. I have generally only used it over fairly small ranges and if I needed to do this over a large range, I think that I would choose the 'helper' method if it increased the performance as much as you indicated.

 

[erik.van.geit]

thanks for feedback, Peter

 

[Peter_SSs]

I have been playing around with this a bit more to see if I can find a better way (without a helper column) but have not come up with anything so far.

However, in looking at NBVC's solution using a helper column with this formula copied down from B2:
=MOD(OFFSET($B2,-1,0)+ IF($A2<>OFFSET($A2,-1,0),TRUE,FALSE),2)
and Erik's suggested shortening to:
=MOD(OFFSET($B2,-1,0) --($A2<>OFFSET($A2,-1,0)),2)
it did strike me that the following further shortening should do the same job:
=MOD(B1+(A2<>A1),2)

 

[NBVC]

Thanks Peter, for continuing to try to improve on this quest

Your version of the formula makes it seems so easy that it makes you wonder why this thread even got to this length to solve this topic.


EDIT

Actually, Peter, upon further testing, I think that your simplified formula would not be able to handle insertions and deletions of rows so easily.
When I delete rows, I suddenly get the #REF! error below it, because row references are lost. The offset functions keeps the references in tact. When inserting a row, the formula would have to be copied down all the way if using your formula, but with mine (or Erik's) you would only have to copy the formula into the inserted row. So I believe the Offset function is necessary for handling adding and subtracting (and probably sorting) of data.

 

[GlennUK]

Without a helper column this will do it:

=MOD(ROUNDUP(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1)),0),2)=1

 

[NBVC]

Without a helper column this will do it:

=MOD(ROUNDUP(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1)),0),2)=1

Hi Glenn,

Yes that works. The only issue I found so far is with inserting a new name within the table. It won't automatically reset the conditional formatting around that name. But if you sort the data after inserting, it resets fine.

 

[GlennUK]

Hi NBVC,

No, I don't get that issue. How are you inserting the new name in the table exactly? Inserting a row and typing a name works fine. Inserting copied cells into the table works fine.

Are you inserting a name into the middle of a group? If so, then I'm not surprised it doesn't work.

 

[NBVC]

Glenn,

I was actually inserting a name into the middle of a group as you stated but I expected that the conditioning would reset. Why doesn't it?

Also, If I have the conditional format set on the entire column and I begin adding names at the bottom of the list, it doesn't automatically highlight the different groups. If I sort the group after entering the names, then it works as expected.

Not a big deal, just an extra step to take.