Alternate Row Colors of Duplicate Data in Excel

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.