Trouble Finding Duplicate Values Using Arrayformula on Google Sheets

1
votes

I am trying to set up a Google Sheet arrayformula to find any duplicates without identifying the first instance of that value. I have a table with the values below:

A

1

2

3

4

5

Since number 2 is a duplicate value, I would like to identify this with a formula but I would not like to indentify the first value in this column. This formula gets me the results I am looking for: =IF(COUNTIF($A$2:$A2,A2)=1, "Unique", "Duplicate")

A B

1 Unique

2 Unique

3 Unique

2 Duplicate

4 Unique

2 Duplicate

But when I try to convert this to an arrayformula so I don't have to manually drag the formula down when new rows are added I get a different result. This is the arrayformula I used: =ARRAYFORMULA(IF($A$2:$A="", "", IF(COUNTIF($A$2:$A,A2:A)=1, "Unique", "Duplicate")))

A B

1 Unique

2 Duplicate

3 Unique

2 Duplicate

4 Unique

2 Duplicate

The problem is that the first value is also identified as duplicate. What would be the best way to convert =IF(COUNTIF($A$2:$A2,A2)=1, "Unique", "Duplicate") into an arrayformula?

1
google-sheetsarray-formulas

1 Answers

3
votes

You want this:

=ArrayFormula(IF(A2:A="",,IF(COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))=1,"Unique","Duplicate")))

The problem with your COUNTIF is that you essentially asked "Is this number unique against every other number in this column? Or is it duplicated anywhere else in this column?" That is why 2 says "Duplicate" in all instances: because each of them is duplicated "somewhere else" in the column.

What you really want to be asking is "Up to this row, has this number been duplicated yet so far?" And that requires COUNTIFS with a second condition that only checks considering ROW() numbers "up to" (i.e., "<=") the current row.