I have a formula that returns either Unique or Duplicate depending on whether there already exists another value in the same column.

The formula =IF(COUNTIF($A$2:$A2,A2)=1, "Unique", "Duplicate") in B2

Example:

  A      B
Peter | Unique
James | Unique
Peter | Duplicate
Anne  | Unique
James | Duplicate

The formula works as it should but I am looking for an alternative formula that works with arrayformula()

The reason is because my data is dynamic which means the ranges change time and time again. It's not possible to manually drag the current formula each time the ranges change so an arrayformula for this would be very welcome.

CodePudding user response：

Lets try BYROW(). BYROW is by default dynamic spill array formula.

=BYROW(A2:INDEX(A2:A,COUNTA(A2:A)),LAMBDA(x,IF(COUNTIFS(A2:A,x)>1,"Duplicate","Unique")))

• Here A2:INDEX(A2:A,COUNTA(A2:A)) will return a array of values as well cell reference from A2 to last non empty cell in column A (Assume you do not have any blank rows inside data). If you have blank row, then you have to use different approach. See this

  CodePudding user response：

  Use this to be able to specify the range in this case A2:A once in

  Used formulas help