how to remove duplicates

dear all,

I have in A thousands of values with many duplicates. Each time i do Data / Advanced / Unique values
it goes forever and nothing happens, i am looking for alternative formula in B so that when new duplicates start in A i will get mark X in B

thanks

Here, try this:

=IF(COUNTIF($A$1:$A$10000,A1)>1, "x", "")

Edit: This will mark both duplicate values: ie. ZZ in third column and ZZ in 7th column.

if you don't want to mark first value (ie ZZ in 3rd column) use:


=IF(COUNTIF($A$1:$A1,A1)>1, "x", "")

and pull down