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

up vote 6 down vote accepted

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 A2A3), 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 A2A3), 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 B20), 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 A3A4), 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 B20), 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

  • Really? No upvotes to this? +1 just for the effort, let alone that it's correct :) – Dave May 12 '15 at 10:39
  • @Dave: Thanks for the recognition.  It has struck me as ironic at best, arguably unfair, that things like What are the Windows A: and B: drives used for? and Why does Windows think that my wireless keyboard is a toaster? get hundreds of votes, while my answers related to Microsoft Office (usually Excel worksheet functions and/or VBA), batch, or shell scripting that may take me close to (or more than) an hour to research and write, often get zero, one, or two at the most.  … (Cont’d) – Scott May 12 '15 at 16:22
  • (Cont’d) …  I guess it comes with the territory of tackling obscure questions that don’t draw many views.  …  P.S. My “account picture” on my Windows PC is also a guitar. – Scott May 12 '15 at 16:23

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.