duplicates

M

max mcneil

Dear People,

Does anyone know how to delete duplicate entries from excel?
With conditional formatting it seems ezy enuf to find dupliates but
how to delete them ... apart from one at a time that is. Ok when the
list is short but when its long as your arm, that can seriously cut into
ones drinking time.

John
 
M

Max

One way ..

Take the sample data below assumed in A2:A5:
Mark Davies
John Smith
David Jones
Mark Davies
David Jones
John Smith
Click to expand...

Put in B2: =IF(COUNTIF($A$2:A2,A2)>1,1,"")
Copy down to B5
This'll mark any duplicates in col A with a "1" in col B

Now do an autoFilter on col B > select "1"
This will filter out all the duplicate rows
Select all these duplicate rows (all the "blue" row headers)
Right-click > Delete Rows
Then just remove the autofilter and you should be left with a list of
uniques in col A
 
M

Max

Typos correction:
Take the sample data below assumed in A2:A5: ....
Copy down to B5
Click to expand...

The sample list pasted is actually in A2:A7
So .. copy down to B7
(suddenly forgot how to count <sg>)
 
H

Herbert Seidenberg

I tried it and it does not work.
"Mark Davies" appears twice in the output column.
 
D

Dave Peterson

The original posts have aged off this thread for me.

Do you have a header in this column? If you don't, excel will treat the first
row as a header and if that first row contains "mark davies", it'll look like
two entries.
 
K

Ken Wright

That or your data just LOOKS the same and you have a trailing space in one of
them perhaps. In any other cell put =firstmark=secondmark changing the Mark's
for cell references. If FALSE then it is likely as I suggested, but if TRUE
then likely as Dave suggested.
 
Top