1

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.

Dan
  • 211
  • 1
  • 8

3 Answers3

2

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 post by @TheMaster

  • Then LAMBDA() will apply COUNTIFS() function for each cell of A column and will display result based on TRUE/FALSE argument.

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Wasn't able to make the second formula work. It's returning all Unique even when they are not. My filter column is alphanumeric. Could that have affected? – Dan Nov 02 '22 at 15:47
1

Use this to be able to specify the range in this case A2:A once in LAMBDA() call.

=ARRAYFORMULA(
 LAMBDA(r   ,IF(r="",,IF(XMATCH(r,r,0,1)=SEQUENCE(ROWS(r))<>TRUE,
       "Duplicate","Unique")))
       (A2:A))

enter image description here

Used formulas help
ARRAYFORMULA - LAMBDA - IF - SEQUENCE - ROWS - UNIQUE

Osm
  • 2,699
  • 2
  • 4
  • 26
1

try:

=LAMBDA(x, INDEX(IF(COUNTIFS(x, x, ROW(x), "<="&ROW(x))>1, 
 "duplicate", "unique")))(A1:A5)

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124