How to file compare Excel worksheets

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.

Originally Posted by wrdennig

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

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


"Ikaabod" wrote:

>
> 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.
>
>
> wrdennig Wrote:
> > 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
>
>
> --
> Ikaabod
> ------------------------------------------------------------------------
> Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
> View this thread: http://www.excelforum.com/showthread...hreadid=534582
>
>

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