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
CodePudding user response：
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 |
To search from bottom, set search_method of XMATCH to -1:
=ARRAYFORMULA(LAMBDA(rg,XMATCH(rg,rg,,-1)=SEQUENCE(ROWS(rg)))(A2:A16))
| Qty | isunique? Bottom first |
|---|---|
| 1 | FALSE |
| 1 | FALSE |
| 1 | FALSE |
| 2 | FALSE |
| 2 | FALSE |
| 3 | FALSE |
| 4 | TRUE |
| 1 | FALSE |
| 2 | TRUE |
| 3 | TRUE |
| 1 | FALSE |
| 1 | FALSE |
| 1 | TRUE |
CodePudding user response：
use:
=INDEX(IF(F2:F="",,IF(COUNTIFS(F2:F, F2:F, ROW(F2:F), "<="&ROW(F2:F))=1, "Unique", "Duplicate")))
update:
=INDEX(IF(F2:F="",,IF(COUNTIFS(F2:F, F2:F, ROW(F2:F), ">="&ROW(F2:F))=1, "Unique", "Duplicate")))
