3

I am working with a dataset in Excel that contains duplicate values and non-duplicate values that are numbers. I would like each duplicate value group to have alternating shades via Conditional Formatting if possible. I've tried to create a "helper" cell to increment for each duplicate group, but can't seem to figure it out.

Here's a slice of the data set (which is in rows A2 through A30)

1
1
1
2
3
6
8
9
10
11
12
15
15
17
18
18
19
20
20
20
20
20
21
21
24
25
25
25
25

And the intended result would be for the 1's to be red, the 15's to be green, the 18's to be red, the 20's to be green, the 21's to be red, the 25's to be green, etc...

bottbott
  • 143

1 Answers1

9

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.
                                                screen shot

Scott - Слава Україні
  • 22,532