HOW CAN DUPLICATES BE DELETED,NOT FILTERED?

Thank you much. It worked.


"Bob Phillips" wrote:

> 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.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "naminel" <naminel.DeleteThis@discussions.microsoft.com> wrote in message
> news:69C4F4D7-4FD7-4C22-8FD6-D81A3BD4FD56@microsoft.com...
> > 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.
>
>
>

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

"naminel" wrote:

>
> Thank you much. It worked.
>
>
> "Bob Phillips" wrote:
>
> > 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.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "naminel" <naminel DeleteThis @discussions.microsoft.com> wrote in message
> > news:69C4F4D7-4FD7-4C22-8FD6-D81A3BD4FD56@microsoft.com...
> > > 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.
> >
> >
> >

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

"Bob Phillips" wrote:

> 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.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "naminel" <naminel.TakeThisOut@discussions.microsoft.com> wrote in message
> news:69C4F4D7-4FD7-4C22-8FD6-D81A3BD4FD56@microsoft.com...
> > 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.
>
>
>

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

"JC" wrote:

> 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
>
> "Bob Phillips" wrote:
>
> > 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.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "naminel" <naminel RemoveThis @discussions.microsoft.com> wrote in message
> > news:69C4F4D7-4FD7-4C22-8FD6-D81A3BD4FD56@microsoft.com...
> > > 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.
> >
> >
> >

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

--


Regards,


Peo Sjoblom

"Anna" <Anna.TakeThisOut@discussions.microsoft.com> wrote in message
news:E3F178E2-BF8E-4997-8DCC-54DB620F50D3@microsoft.com...
> 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
>
> "JC" wrote:
>
>> 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
>>
>> "Bob Phillips" wrote:
>>
>> > 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.
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "naminel" <naminel.TakeThisOut@discussions.microsoft.com> wrote in message
>> > news:69C4F4D7-4FD7-4C22-8FD6-D81A3BD4FD56@microsoft.com...
>> > > 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.
>> >
>> >
>> >

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

"Peo Sjoblom" wrote:

> 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
>
> --
>
>
> Regards,
>
>
> Peo Sjoblom
>
> "Anna" <Anna.RemoveThis@discussions.microsoft.com> wrote in message
> news:E3F178E2-BF8E-4997-8DCC-54DB620F50D3@microsoft.com...
> > 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
> >
> > "JC" wrote:
> >
> >> 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
> >>
> >> "Bob Phillips" wrote:
> >>
> >> > 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.
> >> >
> >> > --
> >> >
> >> > HTH
> >> >
> >> > RP
> >> > (remove nothere from the email address if mailing direct)
> >> >
> >> >
> >> > "naminel" <naminel.RemoveThis@discussions.microsoft.com> wrote in message
> >> > news:69C4F4D7-4FD7-4C22-8FD6-D81A3BD4FD56@microsoft.com...
> >> > > 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.
> >> >
> >> >
> >> >
>
>
>