How do I remove duplicates
You can do this but it won't be just one easy formula.
Assuming you have your phone numbers in column A,
in cell B1 insert and copy down this formula:
=IF(COUNTIF($A$1:$A$11,A1)1,"Duplicate","")
You need that formula because it shows the duplicate phone numbers.
Then in cell C1, insert and copy down this formula:
=IF(ISERROR(INDEX($A$1:$A$9,SMALL(IF(COUNTIF($A$1: $A$9,A1)1,ROW($A$1:$A$9)),ROW(1:1)))),"
",INDEX($A$1:$A$9,SMALL(IF(COUNTIF($A$1:$A$9,A1)1 ,ROW($A$1:$A$9)),ROW(1:1))))
Hold down Control, Shift, and Enter when entering the above formula.
Hitting Enter like normal won't do it.
Then copy it down as far as the phone numbers go.
This makes a list of all the phone numbers that appear more than once.
In column D, copy the list from column C. Copy, Paste Special, Values only.
Organize column D by ascending value and filter by unique values, Just like
Stefi said to do.
Now you have a list of unique phone numbers that appear more than once in
column A. It's not a quick and easy process but I couldn't think of anything
easier!!!
|