I have two columns, Name 1 and Name 2. I need an ARRAYFORMULA formula that tells me whether Name 2 appears elsewhere in either Name 1 or Name 2.
| Name 1 | Name 2 | Is Name2 duplicate (formula) |
|---|---|---|
| Sam | Jay | |
| Sandra | Judy | Yes |
| Jane | Judy | Yes |
| Pete | Sam | Yes |
| Pete | Sarah | |
| Gerry | Gerry | Yes |
It needs to be an arrayformula so it gets added automatically to any new rows.
I can compare either column to a single column:
={"match name 1";ARRAYFORMULA(if(COUNTIF (A:A, B2:B)>0, "yes",""))}
={"match name 2";ARRAYFORMULA(if(COUNTIF (B:B, B2:B)>1, "yes",""))}
I can also make a non-arrayformula formula that compares to both columns:
=OR(COUNTIF (A:A, B2:B)>0,COUNTIF (B:B, B2:B)>1)
However, I can’t seem to get the OR() and COUNTIF to work with ARRAYFORMULA.
>Solution :
Modifying your suggested formula to be:
={"match name 2";arrayformula(if(len(B2:B),if(countif(A:B,B2:B)>1,"Yes",""),))}
Alternative:
=map(B2:B,lambda(Σ,if(Σ="",,if(countif(A:B,Σ)>1,"Yes",))))
