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

One way ..

Take the sample data below assumed in A2:A5:

> Mark Davies
> John Smith
> David Jones
> Mark Davies
> David Jones
> John Smith

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
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"max mcneil" <(E-Mail Removed)> wrote in message
news:41291d13$0$21036$(E-Mail Removed)...
> 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
>
>
>
>
>

Hi
use 'Data - Filter - Advanced Filter' and check 'unique entries'

--
Regards
Frank Kabel
Frankfurt, Germany


max mcneil wrote:
> 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

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

The sample list pasted is actually in A2:A7
So .. copy down to B7
(suddenly forgot how to count <sg>)
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

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

Frank Kabel wrote:
> Hi
> use 'Data - Filter - Advanced Filter' and check 'unique entries'
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> max mcneil wrote:
> > Dear People,
> >
> > Does anyone know how to delete duplicate entries from excel?

Hi
then there's a difference in this data (e.g. spaces, etc.)

--
Regards
Frank Kabel
Frankfurt, Germany

"Herbert Seidenberg" <herbds7-(E-Mail Removed)> schrieb im Newsbeitrag
news:(E-Mail Removed)...
> I tried it and it does not work.
> "Mark Davies" appears twice in the output column.
>
> Frank Kabel wrote:
> > Hi
> > use 'Data - Filter - Advanced Filter' and check 'unique entries'
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> >
> > max mcneil wrote:
> > > Dear People,
> > >
> > > Does anyone know how to delete duplicate entries from excel?
>

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.

Herbert Seidenberg wrote:
>
> I tried it and it does not work.
> "Mark Davies" appears twice in the output column.
>
> Frank Kabel wrote:
> > Hi
> > use 'Data - Filter - Advanced Filter' and check 'unique entries'
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> >
> > max mcneil wrote:
> > > Dear People,
> > >
> > > Does anyone know how to delete duplicate entries from excel?

--

Dave Peterson

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> Herbert Seidenberg wrote:
>>
>> I tried it and it does not work.
>> "Mark Davies" appears twice in the output column.
>>
>> Frank Kabel wrote:
>> > Hi
>> > use 'Data - Filter - Advanced Filter' and check 'unique entries'
>> >
>> > --
>> > Regards
>> > Frank Kabel
>> > Frankfurt, Germany
>> >
>> >
>> > max mcneil wrote:
>> > > Dear People,
>> > >
>> > > Does anyone know how to delete duplicate entries from excel?
>
> --
>
> Dave Peterson


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.806 / Virus Database: 548 - Release Date: 05/12/2004