Shading rows of with similar data
 

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,

Shading rows of with similar data
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

sai wrote:
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,

Shading rows of with similar data
 

Frank,

Thank you so much. Works !!!

Sai

Shading rows of with similar data
 

sai wrote:
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