Reputation: 233
in col A
I have a list of email addresses
in col B
I want to catch duplicates, so if an email address in A appeared before I get a trigger in B
.
I'm using this formula which works great:
=if(isna(match(a3,$A$2:A2,0)),"New","Duplicate")
note that as I drag this formula, $A$2
stays so the range grows (e.g. for cell B51
the range will be from $A$2:A50
)
My problem is that since column A
updates automatically (e.g. new email addresses are added) I want column B
to update automatically as well. I tried using Arrayformula but can't figure it out :(
I tried both:
=arrayformula(if(isna(match(A3:A,$A$2:A2,0)),"New","Duplicate"))
and
=arrayformula(if(isna(match(A3:A,$A$2:A2:A,0)),"New","Duplicate"))
but they don't work.
here's a spreadsheet with an example and my (failing) attempts to solve it https://docs.google.com/spreadsheets/d/1N3pFPnT452FmWa9w8EkYpIq-ZnivjoCzt5ORrNEKgLQ/edit#gid=0
Upvotes: 5
Views: 12232
Reputation: 5529
You can also do a countif that looks at everything above the current row:
=IF(countif($A$1:A2,A2)>1,"DUPLICATE","NEW")
Upvotes: 0
Reputation: 18727
Please, try:
=ArrayFormula(IFERROR(if(VLOOKUP(A2:A,{A2:A,ROW(A2:A)},2,)=ROW(A2:A), "New", "Duplicate")))
If matching row = current row → "New", else → "Duplicate".
I used vlookup
because it may be used with ArrayFormula
Upvotes: 5
Reputation: 34400
You can do it using Match to see if the first match for the current email address is before the current row of the array
=arrayformula(if(match(A2:index(A2:A,COUNTA(A2:A)),A2:index(A2:A,COUNTA(A2:A)),0)<row(A2:index(A2:A,COUNTA(A2:A)))-1,"Duplicate","New"))
Upvotes: 2