Convert a conditional formatting formula into an ARRAYFORMULA to display text on first occurrence of duplicate

Question

I'm trying to create an ARRAYFORMULA for a column that will display text if the value in Column A is unique, and if it is a duplicate display text indicating if the value is the first occurrence of a duplicate or a subsequent occurrence of it.

I've been able to do this with a (probably overly complicated) formula that works as a fill, but because it uses INDIRECT, it can't be iterated over using ARRAYFORMULA.

I came across the following conditional formatting formula on this site that highlights all instances of duplicates except the first occurrence:

=countif($A$2:$A2,$A2) > 1 Where of course Column A contains the value we're checking.

I'm hoping to use this in an IF statement inside an ARRAYFORMULA to run on all the values that are not unique, but I just don't get why the formula works or how to adapt it to my needs.

Answer 1

=ARRAYFORMULA(IF(LEN(A2:A),IF(COUNTIFS(A:A,A2:A)=1,"Great! I'm unique!",IF(MATCH(A2:A,A:A,0)=ROW(A2:A),"I'm the first instance!", "I'm just a clone..")),""))

To combine multipe columns, you can use:

=ARRAYFORMULA(IF(LEN(A2:A),IF(COUNTIFS(A:A&B:B,A2:A&B2:B)=1,"Great! I'm unique!",IF(MATCH(A2:A&B2:B,A:A&B:B,0)=ROW(A2:A),"I'm the first instance!", "I'm just a clone..")),""))