(Msg. 9) Posted: Fri Aug 08, 2008 1:19 pm
Post subject: Re: HOW CAN DUPLICATES BE DELETED,NOT FILTERED? [Login to view extended thread Info.] Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
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?
"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.
>
>
>
(Msg. 10) Posted: Mon Jan 19, 2009 7:46 am
Post subject: Re: HOW CAN DUPLICATES BE DELETED,NOT FILTERED? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
In Excel 2007, I have 2 columns: A contains names, B contains Permissions;
this data was pulled from my database. what I'm trying to do is remove
duplicate names in "A", or replace them with a blank cell, and not disturb
"B". Using the "Remove Duplicates" function, it does part of what I need,
but skews the data corrolation. Example
Before:
A B
jdoe Testing
jdoe Accounting
jdoe Inventory
dmark Telephony
dmark Inventory
scuba Inventory
scuba Finance
AFTER:
A B
jdoe Testing
dmark Accounting
scuba Inventory
Telephony
Inventory
Inventory
Finance
what I WANT to see:
A B
jdoe Testing
Accounting
Inventory
dmark Telephony
Inventory
scuba Inventory
Finance
"Peo Sjoblom" wrote:
>... under the data tab in the data tools section you can apply a function
> called "Remove Duplicates" ....>
> --
>
>
> Regards,
>
>
> Peo Sjoblom
(Msg. 11) Posted: Mon Jan 19, 2009 9:55 am
Post subject: Re: HOW CAN DUPLICATES BE DELETED,NOT FILTERED? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Rogan68 wrote:
> In Excel 2007, I have 2 columns: A contains names, B contains Permissions;
> this data was pulled from my database. what I'm trying to do is remove
> duplicate names in "A", or replace them with a blank cell, and not disturb
> "B". Using the "Remove Duplicates" function, it does part of what I need,
> but skews the data corrolation. Example
> Before:
> A B
> jdoe Testing
> jdoe Accounting
> jdoe Inventory
> dmark Telephony
> dmark Inventory
> scuba Inventory
> scuba Finance
>
> AFTER:
> A B
> jdoe Testing
> dmark Accounting
> scuba Inventory
> Telephony
> Inventory
> Inventory
> Finance
>
>
> what I WANT to see:
> A B
> jdoe Testing
> Accounting
> Inventory
> dmark Telephony
> Inventory
> scuba Inventory
> Finance
>
If this is just for display purposes, use conditional formatting to change the
text to match the background color (usually white) in rows where the value above
is the same.
Select your data in Column A starting from A2. Select FORMAT / CONDITIONAL
FORMATTING. Next to "Cell value is" select "equal to" and then put =A1 in the
box to the right. Select "Format..." and on the font tab for "Color:" select
white (or whatever your background color is for the cells). Click "OK" twice.
(Msg. 12) Posted: Mon Jan 19, 2009 9:55 am
Post subject: Re: HOW CAN DUPLICATES BE DELETED,NOT FILTERED? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Thanks, Glenn. I'll give that a whirl.
"Glenn" wrote:
> If this is just for display purposes, use conditional formatting to change the
> text to match the background color (usually white) in rows where the value above
> is the same.
>
> Select your data in Column A starting from A2. Select FORMAT / CONDITIONAL
> FORMATTING. Next to "Cell value is" select "equal to" and then put =A1 in the
> box to the right. Select "Format..." and on the font tab for "Color:" select
> white (or whatever your background color is for the cells). Click "OK" twice.
(Msg. 13) Posted: Wed Feb 04, 2009 12:08 pm
Post subject: HOW CAN DUPLICATES BE DELETED,NOT FILTERED? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Bob,
This was very helpful for me. Thank you for sharing.
(Msg. 14) Posted: Wed Apr 08, 2009 10:36 am
Post subject: Re: HOW CAN DUPLICATES BE DELETED,NOT FILTERED? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
"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.
(Msg. 15) Posted: Wed Jun 03, 2009 6:35 pm
Post subject: Re: HOW CAN DUPLICATES BE DELETED,NOT FILTERED? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
"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.
>
> --
>
Hi Bob,
Thanks to this...its a huge help...however, when i tried to delete all the
Dups...there are no more remaining for that dup data...it means my count are
inaccurate because the dups are gone without a trace at of atleast one to
count...can you advise me..how to maintain one of those dups to have a
complete count?
(Msg. 16) Posted: Wed Jun 03, 2009 9:15 pm
Post subject: Re: HOW CAN DUPLICATES BE DELETED,NOT FILTERED? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Try Bob's formula once more and remember his formula goes in row 1 of that
helper column.
But before you delete any rows, look at your data. I bet you'll see that the
formula Bob suggested only put "Dup" on the second, third, ... cell with that
data.
If you see something different, then either your data isn't what you think it
is--or you used a different formula.
If the values looked ok, then it must have been the way you did the deleting.
Try this:
Apply the filter to show the Dup's.
Select the visible cells in that column.
Now hit F5 (or ctrl-g) (same as Edit|goto in xl2003 menus).
Click the Special button
Select visible cells only
Then rightclick on one of the still selected cells and choose Delete...
Then entire row.
===========
My bet is that you're using xl2007 and if you're not careful, you'll delete all
the cells--not just the visible cells.
This behavior is different from previous versions--and only occurs under certain
circumstances.
Eve wrote:
>
> "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.
> >
> > --
> >
> Hi Bob,
>
> Thanks to this...its a huge help...however, when i tried to delete all the
> Dups...there are no more remaining for that dup data...it means my count are
> inaccurate because the dups are gone without a trace at of atleast one to
> count...can you advise me..how to maintain one of those dups to have a
> complete count?
All times are: Eastern Time (US & Canada) (change) Goto page Previous1, 2, 3
Page 2 of 3
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office