|
|
|
|
|
|
|
|
|
|
|
|
|
| |
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@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.
> >
> >
> > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
Judy, Bob's advice requires a little bit of tweaking. Run his if statement,
then select the column where you marked the dupes. Copy, then paste special
preserving values. Now, go back and manually select all the data except for
row 1. Cut, then paste up one row. Do a visual scan to confirm that the
workaround was successful, then delete all filtered items as before.
"judyb" wrote:
> 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@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.
> > >
> > >
> > > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
| |
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@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.
> >
> >
> > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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@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@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.
>> >
>> >
>> >
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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@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@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.
> >> >
> >> >
> >> >
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
"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.
Bob,
You're a life-saver. I was trying to match different file types associated
with different portions of my database to see which were accessed in a
specific screen of the interface. Unfortunately, the database just posts what
all files WERE accessed with no apparent sort (by time it was accessed nor
even sorting alphabetically).
I took the list of files accessed before (Column B) and after (Column A).
Using your formula with very slight modifications in Column C:
=IF(COUNTIF($A$1:B123,A1)>1,"Dup","")
I was able to see what was accessed after going to the screen. I then wanted
to be certain nothing was purged after going to the second screen by, again,
modifying your formula in Column D:
=IF(COUNTIF($A$1:$B123,B1)>1,"Dup","")
As I expected, I didn't see anything that wasn't a duplicate. I believe
these formulas are accurate, but I'm obviously not as good as you are.
Either way, it found me what I wanted. Thank you! |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
Question
Answer
Notify me of replies