Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shading rows of with similar data
Frank,
Thank you so much. Works !!! Sai |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Minimizing Rows of with similar data | New Users to Excel | |||
shading data in rows | Excel Discussion (Misc queries) | |||
Combining rows with similar data | Excel Discussion (Misc queries) | |||
Conditional formatting similar to shading alternating rows | Excel Worksheet Functions | |||
How do I compare similar rows of data in same worksheet | Excel Worksheet Functions |