Shading rows of with similar data

[sai]

I have a spreadsheet with multiple rows. I have multiple rows for a
given ID which is shown in Column A. I want to group the rows with the
same ID visually by using color bands. The bands should also
alternate.

For e.g. if my data (in column A) looks like the following

A
A
A
B
B
C
C
C
C

I want three rows with A to be shaded in grey. Two rows of B to be
white and 4 rows of C to again in Grey. The number of rows may vary by
the data that is loaded.
Is there a way I can use conditional formatting to do this?

Thanks,

 

[Frank Kabel]

Hi
- select your range (e.g. A1:X20)
- in the conditional format dialog enter the following formula:
=MOD(SUMPRODUCT(($A$1:$A1<>"")/(COUNTIF($A$1:$A1;$A$1:$A1)+($A$1:$A$1=""))),2)
-choose your color

Assumptions:
- all identical IDs are gouped together

 

[sai]

Frank,

Thank you so much. Works !!!

Sai

 

[Glenn]

Frank,

Thank you so much. Works !!!

Sai

I had to modify the function by adding a ROUND function in order for it
work properly. Excel was not calculating an exact FALSE (i.e., zero)
value in some cases:

=ROUND(MOD(SUMPRODUCT(($A$1:$A1<>"")/(COUNTIF($A$1:$A1,$A$1:$A1)+($A$1:$A$1=""))),2),0)

Still, it's an elegant solution I wouldn't have thought of.
Glenn Ray
MOS Master