Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Tara Keane
 
Posts: n/a
Default How to eliminate duplicate entries

If I import files into excel how can I ensure that no duplicates are imported?
Many thanks
Tara
  #3   Report Post  
bob z
 
Posts: n/a
Default

While this is helpful info, with a file of 23,000 names and addresses, there
were well over a thousand dups that showed up, some with 3 or more dups.
Since all of the duplicate data including the first occurence had a
"Duplicate" in the next column, I used autofilter to list all the dups, but
I still had to delete the actual dups manually. Also, in excel 2000 the
method mentioned at cpearson to add "duplicate" to an adjacent column so you
could autofilter the results caused my computer to repeatedly crash until i
did smaller sections of data at a time.

What i would like to know is : how do you leave the first occurence of the
data, and mark only the dups (trips etc). The suggestions in this forum mark
all occurences of the duplicate data. I would like the first occurence of the
same address to be a 0 and each succeeding occurence to be a 1 or greater so
that i can autofilter and then delete everything over 0, not have to delete
the rows out myself.

" wrote:

See;

http://www.cpearson.com/excel/duplicat.htm


  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

I think you'll find that deleting a bunch of rows in an autofilter goes much
more smoothly if your data is sorted to group the rows to be deleted.

If you have to have your data in the current sorted order, add another helper
column. And put the row number in each cell in that column.

I use
=row()
then copy down
edit|copy
edit|paste special|values

Then I do all the duplicate formula stuff. Then I convert that column to
values, too (just to make things quicker).

Then I sort by that duplicate indicator column and delete the duplicates. Then
remove the filter, and finally sort by that helper (row indicator) column (and
really finally), delete that helper column.

bob z wrote:

While this is helpful info, with a file of 23,000 names and addresses, there
were well over a thousand dups that showed up, some with 3 or more dups.
Since all of the duplicate data including the first occurence had a
"Duplicate" in the next column, I used autofilter to list all the dups, but
I still had to delete the actual dups manually. Also, in excel 2000 the
method mentioned at cpearson to add "duplicate" to an adjacent column so you
could autofilter the results caused my computer to repeatedly crash until i
did smaller sections of data at a time.

What i would like to know is : how do you leave the first occurence of the
data, and mark only the dups (trips etc). The suggestions in this forum mark
all occurences of the duplicate data. I would like the first occurence of the
same address to be a 0 and each succeeding occurence to be a 1 or greater so
that i can autofilter and then delete everything over 0, not have to delete
the rows out myself.

" wrote:

See;

http://www.cpearson.com/excel/duplicat.htm



--

Dave Peterson
  #5   Report Post  
Gord Dibben
 
Posts: n/a
Default

bob

How about using Advanced FilterUnique records only and Copy to a new location
like a new sheet.

For more on this see Debra Dalgleish's site

http://www.contextures.on.ca/xladvfilter01.html


Gord Dibben Excel MVP

On Tue, 1 Mar 2005 18:51:01 -0800, "bob z" wrote:

While this is helpful info, with a file of 23,000 names and addresses, there
were well over a thousand dups that showed up, some with 3 or more dups.
Since all of the duplicate data including the first occurence had a
"Duplicate" in the next column, I used autofilter to list all the dups, but
I still had to delete the actual dups manually. Also, in excel 2000 the
method mentioned at cpearson to add "duplicate" to an adjacent column so you
could autofilter the results caused my computer to repeatedly crash until i
did smaller sections of data at a time.

What i would like to know is : how do you leave the first occurence of the
data, and mark only the dups (trips etc). The suggestions in this forum mark
all occurences of the duplicate data. I would like the first occurence of the
same address to be a 0 and each succeeding occurence to be a 1 or greater so
that i can autofilter and then delete everything over 0, not have to delete
the rows out myself.

" wrote:

See;

http://www.cpearson.com/excel/duplicat.htm



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
find duplicate cells in Excel shawneyv Excel Discussion (Misc queries) 2 January 5th 05 02:39 AM
Finding duplicate records in Excel KG Excel Discussion (Misc queries) 2 December 22nd 04 08:44 PM
How do you find duplicate values in excel- 2 columns of numbers rickmanz Excel Discussion (Misc queries) 1 December 16th 04 12:16 AM
Multiple Files, Duplicate Entries PMSunshine77 Excel Discussion (Misc queries) 1 December 10th 04 09:28 PM
Duplicate records in Excel Sheidsa Excel Discussion (Misc queries) 1 November 30th 04 01:23 AM


All times are GMT +1. The time now is 11:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"