Merge Two Spreadsheets in Excel?

Question

No one has seems to have answered this type of question, even though it's fairly simple I would assume.

I have two excel documents........one is a contact list with 5,200 entries (rows) with headers for each column and then things like name, address, e-mail address, etc.

Then I have another list that is also a list (of contacts) except it has only 1,200 assorted entries (rows). The columns on it are Name, Station, Frequency, etc.

How can I merge the two....i.e. query the contact list (that has full addresses, e-mails etc) to obtain information for the 1,200 in the second spreadsheet? The first column is the same for both (Name), just I need to merge the information together to form a more comprehensive list.

Make sense? Thanks.

The ultimate goal is to take the list (spreadsheet) of 5,200 contacts (that has e-mail addresses), and get the e-mail addresses for the 1,200 names.

I'm trying to avoid manually copying and pasting. For simplicity sake let me phrase it like this......in one excel sheet you have:
Name: Address:
Tom 43453 Main St
Dick 234 S Road
Harry 93 Highway
Janet 98345 Broadway
Pete 32939 Loveland

Then in your SECOND spreadsheet you have:
Name: Food:
Dick Pig
Janet Burgers
Pete Celery

I need to take the first spreadsheet and merge it with the second spreadsheet to combine the information for each name (take that example and multiply it time 1,000).

Answer 1

No one answered this before?
I must missed that, It is simple one (Actually very simple one)
Now, in Sheet1 (Your 5200 ittems sheet) go to the last free column (assume it is AA), and paste this:
=VLOOKUP( $A1, Sheet2!$A:$D, COLUMN( )-25, FALSE)
(Assuming that your second sheet is Sheet2, and your 1200 items are in columns A to D)
If it is not AA, then mail me here to give you the combination

Then Copy and paste this down to the 5200 items and to the right to catch all columns of Sheet2

After that select all these new columns with VLOOKUP function, go to
Edit > Copy
and without changing the selection, go to
Edit > Paste Special > Values > Ok

And you can now delete the Sheet2 (The one with 1200 items)

That is it, I told you, I missed that other question, otherwise, this is easy

Trust me, I am the VBAXLMan

Answer 2

Sort the spreadsheet with 1200 lines by name.
Take the spreadsheet with the 5200 lines
Alongside the first row enter the formula
=VLOOKUP(
*first parameter is a reference to the name cell in that row
*second parameter swap to the sheet with the 1200 lines and select all the data, excluding row headings. Press F4 to change the cell reference to an absolute cell reference,so the cell reference should be eg $A$2:$J$1201 (An alternative is to name the range of values on the 1200 line sheet if you know how to do that.)
*third parameter the number of the column from the 1200 lines that you want to get data from, eg 2 for email,
*fourth param = FALSE means it will return #na if no match
Copy the formula across to other columns alongside the 5200 lines changing the 3rd param appropriately
Copy the VLOOKUP formula down

Answer 3

So... everyone on the list of 1200 names is also on the list of 5200 names? And the headings on one are completely different from the other, besides the names? If so, then I would put both lists in alphabetical order - or better yet, in order according to whether or not they are on the second list (just add a column on the first list for yes/no) - and then cut and paste. A fancier way to do it would be to import both of the worksheets into MS Access as tables, and then run a query to find matching data. I hope this makes sense.

Answer 4

in the beginning do no longer make ameliorations to the unique spreadsheets. reproduction defend them stunning away. ideally each handle may be one line with the call interior the 1st cellular. If no longer, you will possibly be able to desire to make it so by making use of copying and transposing each handle. reproduction all of the information to a minimum of one sheet. Delete the sparkling strains and kind on the 1st column. Delete duplicates by making use of examination. whether you have in straight forward terms 6 pages of addresses that's tedious. If the addresses are in a particular order or format you're arranged to take earnings of that.

Answer 5

If you have Office Professional installed it is much easier to do this in Access.
Basically you import 2 tables into Access. Create a join query and export the results as Excel file.