Merging spreadsheets without common data

[Boop914]

I have two spreadsheets, with duplicate data, that I would like t
combine together. The spreadsheets are customer lists that have th
customer name, city and state code in common. One spreadsheet has th
zip code as five digits, the other spreadsheet uses a nine-digit zi
code. Also, one spreadsheet has a contact name and phone number, th
other doesn't. Is there a way to combine this information so I get on
row of complete data instead of multiple rows of incomplete data

 

[kkknie]

There are a few ways to handle this. Here's the one I would suggest:

1.Open the first worksheet.
2.In the second worksheet, move the different columns so that they ar
outside the bounds of the first worksheet. (I.e. if the last column o
worksheet 1 is G, move the different columns on worksheet 2 to H o
higher.
3. Copy all of the data from the second worksheet to the first.
4. Sort the worksheet by the offending columns.
5. In the contact name and number column delete the ones that don'
have one.
6. In the Zip Code column delete the ones you don't want.

This assumes the same number of rows in each worksheet.

Another option is to create a new tab and reference the correct dat
from either of the two sheets. Then do a Copy | Paste Special | Value
to get rid of the formulas.

 

[Boop914]

Thanks for your reply - my spreadsheets do not have the same number o
rows, plus many of the customers have more than one contact name and
would like to keep all the names. Your second option might work, bu
then would I have to delete the unwanted rows? (Each spreadsheet hav
about 10,000 rows of data).

Thanks again

 

[Boop914]

Thanks Bob,

I thought about a lookup, but then I still have to delete the unwante
or duplicate lines. I was hoping to find a fairly easy way to merg
the two spreadsheets.

My goal in combining the two files is to have compan
name/address/city/state/phone/contact name - listed once for eac
company/address/contact combination.

One spreadsheet might have the company name and address, but no contac
name or phone number. The other spreadsheet - for the same customer
might have the company name, contact and state. Each spreadsheet coul
have duplicate lines of data, in addition to the other spreadshee
having similar data.

I'm probably not explaining this very well - but I do appreciate you
help

 

[Dave Peterson]

I'd do it this way:

Add a helper column in both worksheets with a formula that concatenates the
"key" column info (customer name, city, state):

=a2&"----"&b2&"----"&c2
and drag down
("----" will keep each field separate)

Now you've go a key from each worksheet.

Create a new worksheet.
Copy each key column and paste values into column A of that worksheet (one on
top of the other).

Add a header row for this column.

Now select your column A and use Data|Filter|Advance Filter to get a list of
unique cust, city, state. Put it in a new location (say B1):

Debra Dalgleish has some nice instructions (with pictures!) at:
http://www.contextures.com/xladvfilter01.html#FilterUR

Now you can use a bunch of =vlookup()'s to retrieve any data you want.

I'd leave the #n/a's visible (for no matches).

When you're done (and verified!), copy|paste special|values and then
edit|replace #n/a with nothing (leave blank).