Highlighting duplicates but in alternating fashion

[abaker77]

Hello,
This one has me stumped, and I'm not easily stumped.
I have a simple column of dates; some dates are duplicated, some are not.
What I want is to be able to highlight only the duplicated dates, in alternating fashion.
You might think that Conditional Formatting already provides this - just go to Highlight Duplicates.
But that will not achieve the objective since I don't want all the duplicated dates highlighted in one color.
I want one color to highlight the first group of duplicated dates, a second color to highlight the next group of duplicated dates, then back to the 1st color for the next group, etc. An easy illustration is attached.
Thank you !

 

[etaf]

try this
taken from another forum

Alternate Row Colors of Duplicate Data in Excel

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

superuser.com

needs a helper column B with the 2 formula

Book1
	A	B
1
2	4/1/20	1
3	4/1/20	1
4	4/1/20	1
5	3/1/20	-1
6	5/1/20	2
7	5/1/20	2
8	5/1/20	2
9	5/1/20	2
10	5/1/20	2
11	6/1/20	-2
12	6/2/20	-2
13	6/3/20	-2
14	6/4/20	-2
15	6/5/20	-2
16	6/6/20	-2
17	6/7/20	-2
18	6/8/20	-2
19	6/9/20	-2
20	6/10/20	-2
21	8/1/20	1
22	8/1/20	1
23	8/1/20	1
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IF(A2=A3, 1, -2)
B3:B23	B3	=IF($A2=$A3, B2, IF($A3=$A4, IF(B2>0,3-B2,B2+3), IF(B2>0,-B2,B2)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=$B1=2	text	NO
A:A	Expression	=$B1=1	text	NO

 

[Fluff]

Cross posted Highlight duplicated dates - in alternating fashion
and here Conditional formatting formula to highlight alternate groupings of duplicate dates - OzGrid Free Excel/VBA Help Forum

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

If you have posted this on any other sites, please provide the links.
Thanks

 

[abaker77]

try this
taken from another forum

Alternate Row Colors of Duplicate Data in Excel

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

superuser.com

needs a helper column B with the 2 formula

OMG - IT WORKS; THIS IS POSITIVELY BRILLIANT ! I amazes me how you could come up with this, seemingly so easily and quickly. I tip my hat off to you !
Thank you so much.

 

[etaf]

you are welcome