Google Spreadsheets: Alternating Background Color on Value Change?

Question

there are lots of topics about Conditional Formatting in Google Spreadsheets, but not one like what I'm looking for.

All I want to do is set an alternating background color based on the value changing in a column. For example:

If there isn't an easy way to accomplish this, does someone know what I'd write for the conditional formatting formula?

The values will always be consecutive - the idea is that the coloring is simply a visual aide for when a new value is up.

If you're curious, I have a list of system types and their power consumption numbers - I would simply like to have a color demarcation for when the system type changes to the right off-screen.

Answer 1

There's a way to do this without adding any extra rows or columns. Make a conditional formatting rule and for the "custom formula" put:

=iseven(match($A1,unique($A$1:$A$15),0))

The way this works is:

1. unique produces the list ValueA,ValueB,ValueC,ValueD,ValueE
2. match looks for each value (starting with A1) in that list, note that the search option 0 is required if your values are not sorted. match returns the index of the matching value
3. iseven applies alternating row coloring based on the matching index

Answer 2

I Added an additional column with the following formula: =IF($A2=$A1,$D1,$D1+1)

A2 since I have a header row D since this is the additional column

than conditional formatting on with the following formula: =isEven($D1)

Answer 3

0 / 1

Please try this formula on B1:

={1;ArrayFormula(IF(OFFSET(A2,,,COUNTA(A:A)-1)<>OFFSET(A2,-1,,COUNTA(A:A)-1),1,0))}

will add one's when new value in a row occurs.

---

Counter

Next formula will make a counter for values in A:A.

Paste it in C1:

ArrayFormula(SUMIF(ROW(OFFSET(B1;;;COUNTA(A:A))); "<="&ROW(OFFSET(B1;;;COUNTA(A:A)));OFFSET(B1;;;COUNTA(A:A))))

---

Formating

Conditional formatting on range A:Z with the following formula: =isOdd($C1)

Answer 4

Easiest for me seems to be clear formatting from and select ColumnA and Format, Conditional formatting..., Format cells if... Custom formula is and:

=and(A1<>"",isodd(counta(unique($A$1:$A1)))=TRUE)

Then select formatting of choice and Done.

(From here.)