Thread: 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

i could not get it to work, i uploaded my file here:

http://amonshare.com/ygimjufflovm/example.xlsx.html
(sorry i could not upload it to the forum)

thanks again

Here, try this:

=IF(COUNTIF(A$2:A2,A2)=COUNTIF($A$2:$A$1000,A2),"X","")

that's it, excellent so in case i want to replace mark X with the original brand name in C , can this be done!! thanks

You can just put:

=IF(COUNTIF(A$2:A2,A2)=COUNTIF($A$2:$A$1000,A2), A2,"")

Thanks a lot