Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to format rows to color group by like values in column 1

I have a worksheet that has information like this:

a
a
b
c
c
c

How do I format it so that all of the rows that have a value of a in the first column are one color, then all the rows that have a value of b in the first column are a different color, etc. ?

Edit not from OP to add clarification from comment:

Everything is already sorted alphabetically, and will stay that way, and I want multiple colors.

like image 487
KARI Avatar asked Aug 29 '13 19:08

KARI


2 Answers

I think you need a helper column, say B seeded with 1 in row1, and =IF(A1=A2,B1,B1+1) in B2 and copied down to suit. Then formulae of the kind below should suit for conditional formatting:

SO18519394 example

like image 101
pnuts Avatar answered Oct 15 '22 04:10

pnuts


Create a helper column with a formula like this;

=MOD(IF(A3=A2,0,1)+B2,2)

In this example column A is the column of sorted values to be grouped by, and column B is the helper column. The formula is entered on row 3. Set the first row of the helper column to the value 0 and the others to the formula. This will result in alternating values in the helper column for each group, ie;

a 0
a 0
b 1
c 0
c 0
c 0
d 1
d 1
e 0

You can then set conditional formatting based on the column value. If the value is 1 then highlight the row; if it is 0 do not highlight it. Or use alternating colors or whatever. You can reference any of the articles on the web that describe how to conditional format the entire row based on the value in the column.

IF(A3=A2,0,1) compares the current row (3) and prior row (2) returning a 1 or 0.

MOD( [...] +B2,2) accomplishes the alternation between 0 and 1 when the grouping column value changes.

like image 10
Aladdin Avatar answered Oct 15 '22 03:10

Aladdin