How to combine Excel 2002 files and remove duplicate records?

[Dave542]

I’ve recently took over a task at my job of managing some Excel files.
I am using Excel 2002. There are several Excel files that need to be
combined into one file however, if I do this, I will have some duplicate
records that I will have to delete.
The only way I know how to do this is to copy and paste and then look for
duplicate records manually and delete them as I find them. Is there an easier
way to do this?
Thank you for you support and help.

 

[Otto Moehrbach]

Yes, there is. I would use VBA to do that job, but I need more info. When
you say there are duplicate records, exactly what is duplicated? In other
words, if you are looking for duplicate records manually, what do you look
for? Do you look at just one column and if that one column has a duplicate,
delete one of those rows? Or do you have multiple columns and you have to
check for a duplicate in more than one column (maybe all?) before you can
say you have a duplicate? Post back with more details. HTH Otto

 

[Dave542]

Wow...This is the first time I've posted here or done anything like this
before.
I was told at work to try asking my question here and I would get some help.
I am surprised.
Thank you very much for your reply.
I don’t have VBA.
I have several columns in each file but, on of the columns is an employee ID
number.
I’ll sort on this column and the delete the records (rows) that are
duplicated. If there are more that on records with the same employee number I
delete the additional records so that there is only one record per employee
ID number.

 

[Otto Moehrbach]

Dave
Since you need to check only one column, you can do this with a
worksheet formula or with VBA. You say that you don't have VBA. I think
you mean you don't know VBA. VBA is a part of Excel, so you have it
available.
Here is the procedure using a formula:
Let's say your data starts in row 1.
First sort all the data by the ID column.
In row 1 of the first empty column, enter the formula:
=IF(A2=A1,"Y","")

Now drag that formula down as far as your data goes.

You will see a "Y" in that column for every duplicate you have. For
instance, if you have 3 rows with the same ID, you will see 2 "Y"s.

Now do an AutoFilter (Data - Filter - AutoFilter) on the "Y" in the ID
column.

This will display all the "Y" rows together.

Delete all those rows.

Click on Data - Filter - ShowAll

Done.

This is easy to do from my end. It may not be from your end. Post back if
you need more. HTH Otto

 

[Otto Moehrbach]

Dave
I forgot to mention that you should make a copy of your file and try the
procedure on the copy. Only when you feel confident that you have it, do it
on your real file. Otto