Google Sheets: background color change when value changes?

Question

There are many topics regarding conditional formatting in Google spreadsheets, but it doesn't seem like what I'm looking for.

All I want to do is set the background color based on changing the value in the column. For instance:

If there is no easy way to accomplish this, does anyone know what I will write for the conditional formatting formula?

The values ​​will always be consistent - the idea is that the coloring is just a visual aid when updating a new value.

If you are interested, I have a list of system types and their energy consumption numbers - I just would like to have color differentiation when the system type changes to the right screen.

Answer 1

There is a way to do this without adding extra rows or columns. Create a conditional formatting rule and for "custom formula" insert:

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

How it works:

1. unique creates a list of ValueA, ValueB, ValueC, ValueD, ValueE
2. match searches for every value (starting with A1) in this list, note that the search option 0 necessary if your values ​​are not sorted. match returns the index of the matching value
3. iseven applies alternation of coloring of lines based on the correspondence 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, as this is an extra column

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

Answer 3

0/1

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 it when a new value in the string occurs.

---

Counter

The following 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))))

---

Formatting

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

Answer 4

The only way I see this is to create a new column. This may be hidden if you want.

Drop this formula into any new column and run autocomplete:

 =IFERROR(IF(A1<>INDIRECT("A"&ROW()-1),IF(INDIRECT(CHAR(COLUMN()+64)&ROW()-1)=0,1,0),INDIRECT(CHAR(COLUMN()+64)&ROW()-1)),1) 

Change A1 as needed to start your data. You can also replace the INDIRECT(...) offset with an offset, but then you must specify in which column you are inserting the formula. Therefore, to give an example in this way; if you put it in column B:

 =IFERROR(IF(A1<>INDIRECT("A"&ROW()-1),IF(OFFSET(B1,-1,0)=0,1,0),OFFSET(B1,-1,0)),1) 

This formula gives us a column of 0 and 1, which tells us to either make the background color or leave it white.

So, in your conditional formatting, just apply it to the desired range and use "Custom formula is":

 =$B1 

Choose formatting as desired

Answer 5

It seems easy for me to clearly format and select ColumnA and Format, Conditional formatting ..., Format cells, if ... Custom formula and:

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

Then select the formatting selection and Done .

(From here .)

Answer 6

the most reliable would be to use this formula:

 =ISEVEN(SUMPRODUCT(--(A$1:A1<>A$2:A2)))