countif to return "x" for unique & "xx" for duplicate values in a column

Use this formula instead:

Please Login or Register  to view this content.

MATCH will return the row of the first position that contains the value, and then compare it to the current ROW.
If it's the same, it's the first occurrence, and returns "x".
If it's different, then it's a duplicate, and returns "xx".

There might be other error-cases to handle.


@johnmpl, the "marching COUNTIF" appears to have worse performance than the "MATCH=ROW" construction (this is anecdotal only, we don't really know why). Usually it doesn't really matter, but since there are appear to be over 100,000 rows of data, it could very well be noticeable.

@Shruder, you may want to check performance.

And if possible, use range references like H1:H500000 instead of H:H; forcing the function to assess the entire column will require significantly more calculations, and it actually matters more when you have tens or hundreds of thousands of rows of data, because you're performing relatively large calculations so many times.

ben_hensel... you're right about performance. Is better MATCH than COUNTIF.

Maybe this new option could be faster:

1. Select all range I2:I104730 (assume 104730 your last row)
2. Apply this formula in active cell I2, and press Ctrl + Shift + Enter instead of Enter:
=IF(FREQUENCY(H2:H104730,H2:H104730),"x","xx")

Blessings!