Need to merge excel spreadsheets with a common field?
Ghen
2010-03-15 12:10:46 UTC
I have two excel spreadsheets with different data in them. They have one field in common (Customer_ID). Are there any programs or methods out there that merge spreadsheets like this?
Using Office 2007
Three answers:
Andrew L
2010-03-15 12:58:39 UTC
It all depends how the data is set out. If both sheets have a single line of data per customer id, this should be relatively easy to accomplish using something like VLOOKUP. For instance
=VLOOKUP(A1,Sheet 1!A1:D100,3)
will return the value from column C in sheet 1. You can drag this formula down (and across) your second sheet. Once you have transferred all your data, you can select the whole sheet, Copy, Paste Special-> Values, to "fix" your data.
If the layout is any more complex than single rows, you may have to copy the sheets into an Access database & link them on the common field within Access. then export it back into Excel.
2016-04-15 04:34:20 UTC
If you have indeed left blank cells in your spreadsheet, then when you do the mailmerge it shouldnt put anything in or leave empty lines, so I am wondering if in fact you have put zeros in the column of house names. I do a lot of mailmerges using 2007 and have just tried to replicate your problem (have never used house names before) and left several blank fields but when merging everything is perfect. When you insert your merge fields, are you clicking on the Address Block (which could be a problem) or are you doing it field by field by clicking on the Insert Merge Field icon, which always works better. Other than that I'm afraid I don't know the answer to your problem.
Cozmosis
2010-03-15 13:09:02 UTC
How to Consolidate Data in Microsoft Excel 2007 - Video