Alternate between 1 and 0 for each unique row

[regresss]

Hi, I would like to alternate between colors (or 0s and 1s) for unique row values. That is, if the row above is same, use same value, otherwise use other value. How would you do this?

I have a solution which seems too complex...

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">=IF(A2=A3, 1, -2)
</code>=IF($A2=$A3, B2, IF($A3=$A4, IF(B2>0,3-B2,B2+3), IF(B2>0,-B2,B2)))

 

[Joe4]

Do you mean something like this?

=IF(A2=A3,0,1)

and copying the formula down the rows.

Or is there more to it, something that you aren't telling us.

 

[regresss]

Here's an example

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]jack[/TD]
[TD="width: 64, align: right"]0[/TD]
[/TR]
[TR]
[TD]jack[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]martin[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]martin[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]martin[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]claire[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]claire[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Let's say that your data is in the range A2:B9.
Place zero in cell B2 (the first column B entry).
Then place this formula in B3 and copy down to row B9:

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

 

[regresss]

Beautiful, thought it is about modulo . Cheers.


If anyone wants case-sensitive, try IF(EXACT(A2,A3),B2,MOD(B2+1,2)). But there is probably computational overhead on this so only if needed.

 

[kvsrinivasamurthy]

Data is in A1:A8.
In B1 enter 0.

In B2 , then drag down

=IF(A2=A1,B1,IF(B1=0,1,0))

 

[regresss]

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

The above start automatically with 0 as blank cell is considered 0. Would it be possible to start with 1? Sure I could make something like if ROW()-1=1 then..., but this would get validated with each run; I also do not want to place 1 above.

The idea is to return effortlessly something as below.

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]A[/TD]
[TD="width: 64, align: right"]0[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

I am not sure what you mean by:

but this would get validated with each run

but perhaps something like:

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

Or if you let us know what is in cell B1 (some sort of header, perhaps), we may be able to make use of that, i.e.

=IF(ISNUMBER(B1)=FALSE,1,IF(A3=A2,B2,MOD(B2+1,2)))

 

[Peter_SSs]

For conditional formatting in groups, of course this helper column doesn't have to be 0/1. Here are 2 more options to consider
Row 1 is manually entered 1 or -1 for the first example and 1 or 0 for the second.

Excel Workbook
	A	B	C
1	Data	1	1
2	a	-1	0
3	a	-1	0
4	b	1	1
5	b	1	1
6	b	1	1
7	b	1	1
8	c	-1	0
9	c	-1	0
10	d	1	1
11	d	1	1
12	e	-1	0
Cond Format Groups