Set B2
to
=IF(A2=A3, 1, -2)
and set B3
to
=IF($A2=$A3, B2, IF($A3=$A4, IF(B2>0,3-B2,B2+3), IF(B2>0,-B2,B2)))
and drag it down to B30
(or the last row that contains data, whatever that is).
This will evaluate to a positive number
if this row is part of a duplicate value group
and a negative value if it isn’t
(i.e., if Column A
contains a unique value).
Throughout the first duplicate value group, Column B
will be 1;
throughout the second, it will be 2;
throughout the third, it will be 1 again, and so on (alternating).
On rows with unique values, Column B
will contain
the negative of the value of the most recent duplicate value group.
Step by step:
First row:
- If
A2=A3
, then Rows 2
and 3
are part of the same duplicate value group,
and so B
should be 1, because we want the first group to be numbered 1.
Otherwise (if A2
≠A3
), Row 2
is not part of a duplicate value group
(we don’t know yet about Row 3
), so it should have a negative value.
We make it -2, so that the first duplicate value group (when we find it)
will be numbered 1.
Subsequent rows:
- If
A2=A3
,
then this row and the previous one are part of the same duplicate value group,
and so B
should be the same as it was on the previous row.
- Otherwise (if
A2
≠A3
), if A3=A4
, then this row and the next one
are the first two rows of a new duplicate value group,
and so B
is IF(B2>0,3-B2,B2+3)
:
- If
B2>0
,
then the previous row was the last row of a different duplicate value group.
So we want to alternate values between 1 and 2 —
if the previous row was 1, we want this one to be 2, and vice versa.
The expression 3-B2
implements this alternating behavior:
3-1
is 2 and 3-2
is 1.
- Otherwise (if
B2
≤0
), the previous row has a unique value in Column A
,
and Column B
has the negative of the B
value of the most recent group.
Again, we want to alternate values between 1 and 2 —
if the previous row was -1, we want this one to be 2, and vice versa.
We get this with B2+3
: -1+3
is 2 and -2+3
is 1.
Otherwise (if A3
≠A4
),
then this row is a unique value row, and so B
is IF(B2>0,-B2,B2)
:
- If
B2>0
, then the previous row
was the last row of a different duplicate value group,
and we want this row’s B
value to be the negative of that.
- Otherwise (if
B2
≤0
), the previous row is also a unique value row,
so we want to keep the same B
value.
I guess I could have said -ABS(B2)
here.
So now, obviously, you use conditional formatting
to color cells red if the value in Column B
is 1 and green is it is 2.