Excel Merging Two Spreadsheets.

[Arkai]

My intent,
I have two account lists containing the following columns
name, address, address2, city, state, zipcode, phone, fax, contact

I want to merge them, and remove duplicates by checking against the name column.

How do I go about doing this in excel?

I've scoured google and only go ads to buy products or complicated formula's that didn't include instructions on how too modify.

 

[EagleKeeper]

If you have Access available; export the data and run a query to show only those that are not duplicate of are the same.

Then go from there.

 

[petesamprs]

Why not just copy/paste the data from one file into the other, and sort by the name column.

Then just do a quick test to see if one entry is the same as the prior one: if(A2=A1,"X","") along an adjacent column. Once you've got your column of X's, copy/paste values, sort by Xs, and delete those rows.

 

[PowerEngineer]

I'm going to assume that the two lists can both be copy/pasted into a single sheet on a new spreadsheet, so that you have all the entries on both lists in the first nine columns (name, address, address2, city, state, zipcode, phone, fax, contact) of the new sheet. Let's say there are a total of 1234 names.

Put this equation in J10:

=IF(COUNTIF(A$1:A10,A10)>1,"DUP","")

What the COUNTIF formula does is count the number of times the name in that row (10) appears in the name column (A) up to and including that row (10). Obviously the count will be at least 1 (since A10 is by definition equal to A10). If the count is equal to 1, then this is the first occurance of this name (and J10 becomes ""). If the count is greater than 1, then this same name occurred earlier in the list (and J10 becomes "DUP").

Copy the formula in J10 over the range J1 through J1234. All the duplicate names will appear with "DUP" in column J. Now copy J1:J1234 and paste special - value over itself so that the "DUP"'s become fixed values. Sort the range A1:J1234 by column J to group all the "DUP"'s together. Delete all the "DUP" rows.

You could extend this approach to check for true duplicates (having additional or all columns matching) if you wanted to...

 

[NeoV]

you have a few options

you could do a vlookup in both spreadsheets, looking for a match in the name column like this

=if(iserror(vlookup(A1,rangeofnamecolumninotherfile,1,false),0,1)

That would put a 1 in the cell next to every name that found a match, then you could filter on this column, select 1, and there are all your dup's - then repeat in the other file

 

[Arkai]

when doing this is it case sensitive?

Will it see, BOB as bob?

Thanks!

 

[NeoV]

vlookups are not case sensitive for text strings