Search code examples
google-sheetsduplicatesgoogle-sheets-formulamatchsequence

How to mark first duplicate as unique and rest as duplicate?

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

Solution

  • use:

    =INDEX(IF(F2:F="",,IF(COUNTIFS(F2:F, F2:F, ROW(F2:F), "<="&ROW(F2:F))=1, "Unique", "Duplicate")))

    enter image description here

    update:

    =INDEX(IF(F2:F="",,IF(COUNTIFS(F2:F, F2:F, ROW(F2:F), ">="&ROW(F2:F))=1, "Unique", "Duplicate")))

    enter image description here