Solved: Alternate shading via conditional format with a twist

[Digita]

Hi guys,

I have a long list of names with many duplicate entries. I would like to apply alternate color coding via conditional format to separate different group names, something similar to the alternate row coloring in conditional format (with the use of function MOD(ROW(),2)=0) but rather on group name similarities. For example, in a column, I have:



AA
AA
AAA
AAA
AAB


Using conditional format, I?d like to mark the 3 rows containing AA & AAB with green background and leave the 2 rows in between containing AAA in white.

As usual, thank you in advance for any suggestions.

Regards


kp

[Bob Phillips]

Use a CF formula of

=MOD(SUMPRODUCT(1/COUNTIF($A$1:A1,$A$1:A1)),2)=1

[mdmackillop]

SUMPRODUCT again! :banghead:

To apply this to other cells in the row (my understanding), add a couple of dollar signs
=MOD(SUMPRODUCT(1/COUNTIF($A$1:$A1,$A$1:$A1)),2)=1

[Digita]

Hi Md & XLD,

Thank you for your quick responses. I have tried both versions of the suggested formula in the conditional format for cell A2 and format copied down the list. However the color coding does not seem to kick in properly from row 22 downward.

For reference, I attach a sample workbook in this post. Did I miss something? Thank you kindly for your time and expert advice.

Best regards


kp

[mikerickson]

You can color the column with 4 colors. The base color and three conditional formatting colors with these CF formulas (entered in A2 and copied where needed)

=MOD(SUMPRODUCT(--(A$1:A1<>A$2:A2)),4)=1
=MOD(SUMPRODUCT(--(A$1:A1<>A$2:A2)),4)=2
=MOD(SUMPRODUCT(--(A$1:A1<>A$2:A2)),4)=3

[Bob Phillips]

It is an FP problem. Trys this small variation

=ROUND(MOD(SUMPRODUCT(1/COUNTIF($A$2:$A2,$A$2:$A2)),2),0)=1

[Digita]

Hi XLD

Your amended formula does the trick. Thank you so much.

Hi Mikerickson

Your solution is superb. My special thanks to you also.


Best regards


kp