hofebido
0
I use IF(COUNTIF(F2:F2:F,F2:F)=1, "Unique", "Duplicate") to mark unique and duplicate entries
but what if I want to mark the first match as unique and the rest as duplicates?
say that 10 people report the same link, but only the first person's link will be considered as unique, and the rest 9 people's link will be considered as duplicates
is that possible? the data is sorted with newest at the top, so I guess this should start searching from bottom to top to find first entries from bottom
MATCH or XMATCH only finds the first value. Use that property to compare the result with row number:
=ARRAYFORMULA(LAMBDA(rg,XMATCH(rg,rg)=SEQUENCE(ROWS(rg)))(A2:A16))
| Values | isunique? |
|---|---|
| 1 | TRUE |
| 1 | FALSE |
| 1 | FALSE |
| 2 | TRUE |
| 2 | FALSE |
| 3 | TRUE |
| 4 | TRUE |
| 1 | FALSE |
| 2 | FALSE |
| 3 | FALSE |
| 1 | FALSE |
| 1 | FALSE |
| 1 | FALSE |
use:
=INDEX(IF(F2:F="",,IF(COUNTIFS(F2:F, F2:F, ROW(F2:F), "<="&ROW(F2:F))=1, "Unique", "Duplicate")))
=INDEX(IF(F2:F="",,IF(COUNTIFS(F2:F, F2:F, ROW(F2:F), ">="&ROW(F2:F))=1, "Unique", "Duplicate")))
