HOW CAN DUPLICATES BE DELETED,NOT FILTERED?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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)
 
Thank you much. It worked.


Bob Phillips said:
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)
Click to expand...
 
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
 
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,

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
 
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
 
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
 
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 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
 
Rogan68 said:
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
Click to expand...


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.
 
Bob Phillips said:
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.
Click to expand...

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!
 
Bob Phillips said:
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.
Click to expand...
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?
 
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.
 
Is there a way that Excel can look at a date base (with values) and ID all
duplicates as a value? Let's say there is a value of 102 in the DB and it
shows up 7 times. I want this value in a sepertae column.

Thanks
Larry
 
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.
 
Back
Top