How to file compare Excel worksheets

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

Guest

I add records, daily, to a master file. Sometimes a record is a duplicate.
At this point, I sort by one column and then scroll down looking for
duplicate records, which I delete. Is there an easier way? I don't want to
have duplicate records.

THanks, Walt
 
What I usually do is in a new column put:

=IF(AND(A2=A1),"DUP","")

This only works if you are trying to compare a column with unique IDs.
However, if you're looking at a list of names where some names might be
duplicated (example: Two people named James, but one lives in USA and
the other lives in Germany) try something like this:

=IF(AND(A2=A1,B2=B1,C2=C1,D2=D1),"DUP","")

You're limited to 30 different ANDs... but that usually does the
trick.

...then copy/paste the formula down. This makes it much easier to
identify duplicates when you scroll down. And if you want to go a step
further:

You can then copy the new column and pastespecial (values) and then
re-sort the data by this new column in descending order. Delete the
duplicate rows then re-sort by your original column. This only checks
to see if the cell is equal to the one above it so it only works after
everything is sorted by your original sort column.

Probably easier to do it manually if you only have 20 or 30 records,
but it'd save you a lot of time if you have 100s or 1000s of them.

I hope that makes sense.
 
THanks. My file has 7 columns. Each row is unique, except for the
duplicates. I'd like to search for duplicate information in column 6
(telephone numbers) . . . how would I write that script? And then what are
the key strokes to make the compare happen? Can I then delete the duplicate
row? And how do I get it to go to the next duplicate?

Sorry for all the questions. I'm a very un-geeky guy . . . walt
 
Sort the data using telephone number as the sort field. Put a heading
like "check" in H1 and a variation of Ikabod's formula in H2:

=IF(G2=G1,"Duplicate","Unique")

which will report the second and subsequent duplicates. Copy this
formula down the column.

Then click Data | Filter | Autofilter to apply filters to each column,
and use the filter pull-down for column H to select "Duplicate".
Highlight the visible rows and Edit | Delete Row, then use the filter
pull-down to select "All". Your duplicate records will now have been
deleted, and you can remove the filters and delete column H.

Hope this helps.

Pete
 
Back
Top