Conditional Formatting / Macro Help - Format a data based on every change in a particular row
- Thread starter anishms
- Start date
Peter Bartholomew
Well-Known Member
What is needed is a formula based on relative references to the current and previous rows. The idea is to increment the block counter when the Person Number is different from the previous. The conditional format should paint the cells yellow if the block counter is an odd number.
NARAYANK991
Excel Ninja
Hi ,
In case you find Peter's solution somewhat overwhelming , see if this is OK.
1. In any unused column , say column D , enter the number 1 in the first row of data ; I have entered 1 in cell D3.
2. In cell D4 enter the following formula , and copy down.
=IF(C4=C3, D3, -D3)
3. For the formatting , select the data range $C$3:$C$17 , enter the following rules and select two different colours.
=$D3 = 1 for one colour
=$D3 = -1 for the second colour
Narayan
In case you find Peter's solution somewhat overwhelming , see if this is OK.
1. In any unused column , say column D , enter the number 1 in the first row of data ; I have entered 1 in cell D3.
2. In cell D4 enter the following formula , and copy down.
=IF(C4=C3, D3, -D3)
3. For the formatting , select the data range $C$3:$C$17 , enter the following rules and select two different colours.
=$D3 = 1 for one colour
=$D3 = -1 for the second colour
Narayan
NARAYANK991
Excel Ninja
Hi ,
If you do not want to use a helper column , then select your entire data range and format the cells in one colour.
Then , keeping the entire data range selected , add the following conditional formatting rule and select a different colour.
=MOD(SUM(IF(FREQUENCY(C$2:C3,C$2:C3) >0, 1)), 2) = 1
or
=MOD(SUM(--(FREQUENCY(C$2:C3,C$2:C3) >0)), 2) = 1
Narayan
If you do not want to use a helper column , then select your entire data range and format the cells in one colour.
Then , keeping the entire data range selected , add the following conditional formatting rule and select a different colour.
=MOD(SUM(IF(FREQUENCY(C$2:C3,C$2:C3) >0, 1)), 2) = 1
or
=MOD(SUM(--(FREQUENCY(C$2:C3,C$2:C3) >0)), 2) = 1
Narayan
Peter Bartholomew
Well-Known Member
NARAYAN
I loved your use of FREQUENCY; it took me a while to understand its role in the calculation. The version below builds from the same basis but uses SIGN and ISODD functions instead of value comparisons
=ISODD(SUM(SIGN(FREQUENCY(C$2:C3,C$2:C3)))
Because, unlike the rest of humanity, I can neither read, nor see the relevance of direct cell referencing, I defined a Named range 'above' to refer to
=Table1[[#Headers],[Person Number]]:Table1[@[Person Number]]
(or a dynamic array equivalent =top:"@"PNum)
To my mind, this allows a further 'simplification' (or, to others, adds 'complexity')
= ISODD( SUM( SIGN( FREQUENCY(above, above) ) ) )
Another thought is that it would be far easier to move away from the alternating colour fills and, instead, use conditionally formatted borders between the data blocks.
I loved your use of FREQUENCY; it took me a while to understand its role in the calculation. The version below builds from the same basis but uses SIGN and ISODD functions instead of value comparisons
=ISODD(SUM(SIGN(FREQUENCY(C$2:C3,C$2:C3)))
Because, unlike the rest of humanity, I can neither read, nor see the relevance of direct cell referencing, I defined a Named range 'above' to refer to
=Table1[[#Headers],[Person Number]]:Table1[@[Person Number]]
(or a dynamic array equivalent =top:"@"PNum)
To my mind, this allows a further 'simplification' (or, to others, adds 'complexity')
= ISODD( SUM( SIGN( FREQUENCY(above, above) ) ) )
Another thought is that it would be far easier to move away from the alternating colour fills and, instead, use conditionally formatted borders between the data blocks.
Last edited:
Peter Bartholomew
Well-Known Member
@vletm
Interesting that I normally avoid VBA to speed a workbook up whereas you seem to move in the opposite direction.
In this case you would gain by avoiding the partial summations at every row, an O(n²) operation, and presumably also avoid the volatile nature of conditional formatting (your VBA project seemed to be password protected)
Following up on the idea of lower key conditional formatting the example below requires only that the row with a new person ID is formatted, which is far easier to achieve.
Interesting that I normally avoid VBA to speed a workbook up whereas you seem to move in the opposite direction.
In this case you would gain by avoiding the partial summations at every row, an O(n²) operation, and presumably also avoid the volatile nature of conditional formatting (your VBA project seemed to be password protected)
Following up on the idea of lower key conditional formatting the example below requires only that the row with a new person ID is formatted, which is far easier to achieve.
Peter Bartholomew
Speed? I'm interesting the result.
I refer to original data, which You could see from my file.
Conditional formatting with large data ... the original data has 332 rows and 'normally' there are ~1200 rows.
... and same time could do other 'duties' too with one run.
Speed? I'm interesting the result.
I refer to original data, which You could see from my file.
Conditional formatting with large data ... the original data has 332 rows and 'normally' there are ~1200 rows.
... and same time could do other 'duties' too with one run.