searching for duplicates

  • Thread starter Thread starter chutchings
  • Start date Start date
C

chutchings

How do you search for and eliminate duplicate entry's in a spreadsheet?
I have 6000 lines to search through and have a couple of hundred
duplicates that I need to delete. Is there an easier way than sorting
them and going through all 6000 lines?
 
chutchings said:
How do you search for and eliminate duplicate entry's in a spreadsheet?
I have 6000 lines to search through and have a couple of hundred
duplicates that I need to delete. Is there an easier way than sorting
them and going through all 6000 lines?
Click to expand...

What do you mean by duplicate? Each and every field identical? If less
than that, how do you decide which of the duplicates to delete? E.g.

John Smith X50885
John Smith X50776

If these two rows are 'duplicates' because they have identical first and
last names, which row do you delete?
MS Access is great for doing this sort of thing, but if it's a spreadsheet
you could do the de-duping inside the spreadsheet application (eg Excel)
using pivot tables. If you want to use Access, then you need to look at
linked tables and 'Group By' queries.
 
Yes, I have Excel spread sheets that have duplicated information, ie.
all the information is the same in each row. I'd like to delete one of
the dubs and keep the other. Using your example above except that
there are two John Smith X50885's and I'd like to delete one of them.
I've used the Advanced Filter Option to sort them but is there a way to
delete all the dups so I only have (1) John Smith X50885 and not (2)?
 
chutchings said:
Yes, I have Excel spread sheets that have duplicated information, ie.
all the information is the same in each row. I'd like to delete one of
the dubs and keep the other. Using your example above except that
there are two John Smith X50885's and I'd like to delete one of them.
I've used the Advanced Filter Option to sort them but is there a way to
delete all the dups so I only have (1) John Smith X50885 and not (2)?
Click to expand...

In my version of Excel (XP) I can select unique records only from the
advanced filter. Failing that you can build a pivot table. Or, as you
asked in an Access newsgroup, you could create a linked table in Access
which points to the spreadsheet. Design a new query with all columns
individually selected, then select view>totals. This query will show one
record per group of duplicates. You could then copy and paste these into a
new worksheet.
 
If the rows are exact duplicates:

1. Import the Excel sheet to an Access table.
2. Copy the table (structure only) to a new blank table (Select table, type
Ctl-C, Ctl-V).

In the new table, in design view:
1. Select all the fields and mark them as a primary key (click on key icon
on toolbar).
2. Close the new table.

Now select all the records from the 1st table (click on top left corner) and
copy them (Ctl-C)
into the new table (click on top left corner of new table and type Ctl-V).

You will get messages saying not all records can be copied and saying do you
want to suppress further error messages (answer yes).

The result will be the unique records in the new table and the duplicates in
the paste errors table.
 
Back
Top