HOW CAN DUPLICATES BE DELETED,NOT FILTERED?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

naminel - 27 Oct 2005 14:20 GMT

I am using Office XP and I was wondering how to delete duplicates. Is there
a procedure in Excel where I can do this in one key stroke? My list consists
of 14,000 lines, so as you can see it would be quite cumbersome to go through
manually to delete them. Any help would be greatly appreciated.

Reply to this Message

Bob Phillips - 27 Oct 2005 14:45 GMT

In an adjacent column, add this formula

=If(COUNTIF($A$1:A1,A1)>1,"Dup","")

and copy down

Then filter column B and select a value of Dup. Delete all visible rows.

Signature

HTH

RP
(remove nothere from the email address if mailing direct)

> I am using Office XP and I was wondering how to delete duplicates. Is there
> a procedure in Excel where I can do this in one key stroke? My list consists
> of 14,000 lines, so as you can see it would be quite cumbersome to go through
> manually to delete them. Any help would be greatly appreciated.

Reply to this Message

naminel - 28 Oct 2005 15:49 GMT

Thank you much. It worked.

> In an adjacent column, add this formula
>
[quoted text clipped - 11 lines]
> through
> > manually to delete them. Any help would be greatly appreciated.

Reply to this Message

judyb - 03 Nov 2005 18:59 GMT

I tried the same, or almost so, and I must have done something wrong. It has
been a while since I have fooled with this type of thing.

I want to see if col B is the same, and changed the formula to
=IF(COUNTIF($B$1:B1,B1)>1,"Dup","")

Dup is appearing, but in the row after the second occurance.

thanks for any help,
judy

> Thank you much. It worked.
>
[quoted text clipped - 13 lines]
> > through
> > > manually to delete them. Any help would be greatly appreciated.

Reply to this Message

JC - 20 May 2008 16:14 GMT

Bob,
I tried this with no luck, do not know what i did wrong.

I put formula in the adjecent colum at the end of my report area and copied
it down.
After that I do not know what you mean by select a value of dup.

Please advise,

JC

> In an adjacent column, add this formula
>
[quoted text clipped - 11 lines]
> through
> > manually to delete them. Any help would be greatly appreciated.

Reply to this Message

Anna - 29 Jul 2008 14:55 GMT

Bob,

I have more than 120,000 lines to process. Is there a faster way than doing
the formula, copying down, filtering 'dup' and deleting? Appreciate your time.

=IF(COUNTIF($A$2:A207,A207)>1,"DUP","")

Thanks,
Anna

> Bob,
> I tried this with no luck, do not know what i did wrong.
[quoted text clipped - 22 lines]
> > through
> > > manually to delete them. Any help would be greatly appreciated.

Reply to this Message

Peo Sjoblom - 29 Jul 2008 15:36 GMT

If you have more than 120,000 rows in one sheet than you must have Excel
2007, under the data tab in the data tools section you can apply a function
called "Remove Duplicates"
If you don't have Excel 2007 then you need portion out these rows then
applying this formula and filtering on "Dup" then deleting the visible rows
shouldn't take that long.
There are add-ins like ASAP Utilities that you can install which has this
built in

Signature

Regards,

Peo Sjoblom

> Bob,
>
[quoted text clipped - 37 lines]
>> > through
>> > > manually to delete them. Any help would be greatly appreciated.

Reply to this Message

Anna - 29 Jul 2008 20:05 GMT

Peo,

Thanks. The 120,000 is from 2 files. I was working on the first file for abt
an hour, copying the countif formula down and my system crashed. I lost
everything I worked on and had to start all over :(

I also tried doing advanced filter but the file is too big and it hanged
each time I tried it.

Anna

> If you have more than 120,000 rows in one sheet than you must have Excel
> 2007, under the data tab in the data tools section you can apply a function
[quoted text clipped - 46 lines]
> >> > through
> >> > > manually to delete them. Any help would be greatly appreciated.

Reply to this Message

Stacey Kettenacker - 08 Aug 2008 21:19 GMT

I have exported Outlook Contacts and want to delete the duplicates, but each
duplicate entry (or row) isn't exactly the same. I would like to filter and
find the duplicates by 2 columns - then once identified, ask Excel to delete
the record with less information. Is that possible?

> In an adjacent column, add this formula
>
[quoted text clipped - 11 lines]
> through
> > manually to delete them. Any help would be greatly appreciated.

Reply to this Message