u/Sptlots

https://preview.redd.it/7hcj6zxu082h1.png?width=170&format=png&auto=webp&s=02515be8fa4471298de2f63e9cfab6d69d545708

Trying to assign conditional formating to any row marked "Group" ("E") by using the row number of the group's first appearance as a stable identifier (D).

I attempted this:

=AND($E2="Group", COUNTIF($D$2:$D$600, $D2)&gt;1, MOD(COUNTUNIQUE($D$2:INDEX($D$2:$D$600, MATCH($D2, $D$2:$D$600, 0))), 4)=1)


=AND($E2="Group", COUNTIF($D$2:$D$600, $D2)&gt;1, MOD(COUNTUNIQUE($D$2:INDEX($D$2:$D$600, MATCH($D2, $D$2:$D$600, 0))), 4)=2)


=AND($E2="Group", COUNTIF($D$2:$D$600, $D2)&gt;1, MOD(COUNTUNIQUE($D$2:INDEX($D$2:$D$600, MATCH($D2, $D$2:$D$600, 0))), 4)=3)


=AND($E2="Group", COUNTIF($D$2:$D$600, $D2)&gt;1, MOD(COUNTUNIQUE($D$2:INDEX($D$2:$D$600, MATCH($D2, $D$2:$D$600, 0))), 4)=0)

However it returns a result where rows with different D values (202 and 304) are formatted the same. Any suggestions on what I'm missing?

https://preview.redd.it/p9gefa86182h1.png?width=186&format=png&auto=webp&s=c00a15d2a25aa62556bade1e57464df7e51b8108

Conditional formatting for unique sets