Merge (?) several contact lists (8000 contacts+) and erase duplicates

[leakalliope]

Hey folks, I'm not quite sure if I'm in the right sub-thread, hope to get some answers here !

I have a problem with my contact lists, can’t solve this since 2 days, already googled for hours but don’t really get an adequate answer from it.
I’m kind of a beginner in excel so please don’t be too hard on me -.-

I have 4 sheets/Lists, and each one has about 200 to 8000 contacts .

Some of the contacts are either in just one of the 4 lists, some are duplicated in 2 or 3 or even in all the 4 lists.

Now I want to merge the 4 Lists find the duplicates and erase them.

One main problem is that some of the lists are sorted by (each column)
Name A1 - Email B1 - Agency C1 - Position D1 - Country E1

and some have just the first 2 or 3 informations (A1 & B1) or A1 & C1 or sometimes A1 & C1 & D1.

So, in the end the question is how do I find the duplicates, merge each one of them if some of them happen to miss (an information) a column and erase the rest?

Can’t really imagine myself doing it manually with 10000 contact

Please help !! I’m really great full about any tipp !
Thanks a lot in advance and all the best,
Kalliope

[pjwhitfield]

If this is a one off job then.....

Play around with your sheets so you have the same data in the same columns

eg
if a sheet doesnt have email then add in a blank column at B so the others are in the same places.

Copy each of the sheets in turn and add them to a new sheet
ie copy Sheet1 and paste it in New sheet 5
then copy sheet2 and paste it on the 1st available row of Sheet5
do this for all 4 sheets.

Then sort this new sheet on a field that will match (probably name but ideally email address if all sheets have it).

Then add the following into the 1st blank column (Ive assumed F)
In F2 put
=IF(B2 = B1, "X","")
The B refers to the column you see as being unique (ideally email)
and drag this down to the bottom
This will put an X in all of the duplicates.
Highlight the column, copy it and then paste special as values
filter on X
and delete

[kasan]

Or you can choose one column witch is in all tables, collect all data from those columns in one, then Data - Remove Duplicates. This will give you list of unique identificators which are in other tables.
After this is done - you can Vlookup or Index/Match info from other tables.