Excel 2013 match values in two workbooks and merge one column

1

I'm running Excel 2013 and I have two spreadsheets open. The first, sheet1, has two columns and 7k rows the columns are:

A                      B

ID                Description

58749651         a bunch of text

The second, sheet2, has 24 columns and 26,000 rows. Column "A" is the same as in the first sheet - title "ID" and the values are numbers. There is no "description" column in sheet2 - which is my question.

How can I create a description column on sheet 2 (it would be column "U") and populate it with the description from sheet 1 where the ID values match?

There's probably 1500 or so ID's on sheet1 that are also on sheet2 and I need to merge just the description from sheet1 into the appropriate row on sheet2.

Anyone have any ideas on how to do this or where I can find the information? Thanks! Rob

Rob50

Posted 2015-01-26T02:05:13.453

Reputation: 73

Hi Rob! Welcome to Super User. Are both the sheets (sheet1 and sheet2) in the same excel file (also called workbook)? – Prasanna – 2015-01-26T02:17:29.417

Hi Prasanna, thanks! No, they are two separate workbooks. – Rob50 – 2015-01-26T02:38:50.433

Answers

0

This would seem to be a job for VLOOKUP

U1:  =IFERROR(VLOOKUP(A1,[Book2]Sheet1!$A$1:$B$7000,2,FALSE),"")

Change Book2 to reflect the proper workbook name where your first list is stored.

Ron Rosenfeld

Posted 2015-01-26T02:05:13.453

Reputation: 3 333

Hey Ron, the first list, the one with just ID & description fields, is "apps1", the second workbook is "nodesc" …I tried changing "book2" to apps1 and it asked me to save the file? In which workbook should I put the formula - and does "sheet1" need to be renamed? Thanks! – Rob50 – 2015-01-26T03:42:16.263

The U1: at the beginning of the formula is the cell address where you should put the formula that refers to A1, obviously on the same worksheet since A1 is not qualified by workbook or worksheet name. Since you are having problems getting the workbook and worksheet references correct, I would suggest using the Insert Function bar and selecting the relevant cells in the relevant workbooks/sheets. Excel will create the proper references for you. – Ron Rosenfeld – 2015-01-26T04:05:14.857

Thanks again Ron…again, I know I'm missing something…when I put the formula in U1, in the "nodesc" sheet, change "book2" to "apps1", and hit enter…a dialog box opens titled "Update Values:apps1"…and, when I select "apps1" in the file name field I get an Excel error: "Excel cannot update one or more links in this workbook. To update the links, open all the link source files (click edit links on the data tab). So I get that error but also "apps1" is not the file I want to "update" - I want to update the "nodesc" file by inputting the description column from apps1. – Rob50 – 2015-01-26T16:55:27.680

Okay, I'm getting closer! I switched "book2" with "sheet1" and I the formula placed the column ID in U1…! But when I try to drag the formula down the length of column it just keeps putting the column title - "Description" - in the cells. – Rob50 – 2015-01-26T17:27:23.030

@Rob50 Let's see the actual formula you are using. Copy/paste it; do NOT try to type it in. – Ron Rosenfeld – 2015-01-26T20:07:57.437

=IFERROR(VLOOKUP(A1,[Sheet1]robapps!$A$1:$B$7000,2,FALSE),"") – Rob50 – 2015-01-26T20:28:19.717

Hey Ron, above is the actual formula - that gives copies over the column name "description"…and when I drag it down a few rows, or even do a fill/down, that is all it populates with…I've been messing with it and made some progress - I can now get to give me an "empty cell" error! ;) – Rob50 – 2015-01-26T20:29:50.463

@Rob50 What is the name of the workbook with a1:b7000; what is the name of worksheet – Ron Rosenfeld – 2015-01-26T20:32:41.677

That would be the workbook with sheet1 - it's: med_id_nodesc.xlsx – Rob50 – 2015-01-26T20:42:30.397

@Rob50 Then (assuming that workbook is open) you formula should look something like: =IFERROR(VLOOKUP(A1,[med_id_nodesc]Sheet1!$A$1:$B$7000,2,FALSE),"") (If the workbook is closed, the full path name and file suffix would replace the file name. Excel will do this automatically if/when you close that workbook). – Ron Rosenfeld – 2015-01-26T20:46:47.343

Ron - I have 40 files that contain the descriptions I need - because the original was 1.2gb, so I split. I thought I'd try this with a different file - on the off chance there were no matches in the first file to actually merge. Now I'm completely lost…it keeps inputting "robapps.xlsx" where I put "med_id_nodesc". Also, whenever I run the formula a dialog box opens saying "cannot find robapps.xlsx"! I think I need to close this down and start over…I'm pretty sure you've given me the info I need - I'm just, in case you didn't notice, completely unfamiliar with Excel! – Rob50 – 2015-01-26T21:05:10.823

@Rob50 It sounds like I don't understand your workbooks and worksheets naming schemes. As I suggested before, I would recommend selecting U1 on the worksheet where you want to show the Description, then select the formula bar -- that should be showing near the top with a little Fx. Then type in the formula BUT, instead of typing any addresses, select the cell(s) that you want to reference. So instead of typing in [workbook]worksheet!$A$1:$A$7000 you would select that range on the appropriate worksheet/workbook. If you do this correctly, Excel should figure out the proper reference. – Ron Rosenfeld – 2015-01-26T21:11:06.900

Maybe that's where I'm getting confused too…one more try? I now have a workbook - "nodesc.xlsx" - and within that I have two sheets. Sheet1 and Sheet2. – Rob50 – 2015-01-26T21:30:18.110

Sheet1 is the master, so to speak…that is the sheet I need to import the "description" column, from sheet2, into. So I need to match any "id" numbers from column A - in both sheets - and where there is a match, import the description, from column b/sheet2, into the Column U on sheet1. – Rob50 – 2015-01-26T21:32:00.357

@Rob50 I'm sure that's part of it. In your response to Prasana yesterday, you indicated you had two different workbooks. If you have only a single workbook, then you would not reference the workbook name, only the worksheet name. But the process I outlined above, of selecting cell ranges using the function bar, would work anyway. – Ron Rosenfeld – 2015-01-26T21:32:31.003

Thanks again Ron! I'll try it all in one workbook with just the two sheets ;) – Rob50 – 2015-01-26T21:54:00.430

Asked: 2015-01-26T02:05:13.453

Viewed: 845 times

Active: 2015-02-13T04:52:00.070