Is is possible to merge two Excel workbooks that contain different data but share some (but not all) elements of a common column? Let me explain. No, there is no time. Let me sum up: I have two Excel workbooks. One is a class roster containing student ID numbers, majors, email addresses, and other related minutia. The other workbook contains student ID numbers, and the device number of a hand-held transmitter that is included with the textbook. The first workbook is generated by the Universities registartion system, so it contains all of the students who are registered in the class. The second workbook is created from an on-line form the students fill out, so it is unlikely that it will contain all of the students in the gradebook: some will forget, or not bother, or not know how to do this. I’d like to merge these two lists so the instructor can easily see which clicker belongs to which student. Can excel take the data from that second workbook and merge it into a new column in the first by matching the student ID numbers, and leaving blank those without info?
If both list were identical, I could sort both lists by SID and C&P, but since on is a subset of the other, that won’t work. I’d like to avoid having to cut and paste by hand since this is a task that tenured research faculty will end up having to do, so to keep their involvment to a minimum I’d lik to automate the process. Any thoughts, advice, etc? Thanks!
You can do it with some awkwardness in Excel, but if there’s ever been a task with FileMaker written all over it, it’s this one. Nine minutes fifteen seconds from tugging at the shrinkwrap off the box to printing out your list as described.
The VLOOKUP function should do exactly what you want. It’s a bit tricky to use at first, but once you get the hang of it, it’s awesome.
In the instructions below, ‘Table 1’ is the table in which you’re adding new columns, based on the values in ‘Table 2’, the lookup table.
Make sure the first row of both worksheets contains column names
Put the common field (Student ID) in the first column of each worksheet. Excel only requires this in Table 2, but it doesn’t hurt to do it in both.
In Table 1, select the cell in row 2 of the first empty column.
From the menu, select INSERT > FUNCTION > VLOOKUP
You’ll get a little box with 4 fields. Make sure the cursor is in the correct field before making each selection.
Click field #1
Select the cell in row 2 that has the common value (Student ID).
Click field #2
Switch to the worksheet with Table 2, and select the whole table by clicking on the column headings and dragging across. You’ll automatically switch back to the first worksheet.
Click field #3
Type the number of the column you want to get the data from in Table 2. If the data you want is in Column C, enter “3”.
Click field #4.
Type the word “false” (unless you want to bring back values that are close, but not exactly the same)
When you click okay, the value from Table 2 should appear. Now you can copy the cell down and fill in the rest of the table.
“N/A” = no matching value in table
Anything else (#REF, #Value#, etc.) probably means you screwed up. Try again, and check the format of your cells
BaldTaco - just wanted to say you may have just saved my Masters thesis. Spent the best part of 2 months trying to work out how to do this and that’s the best explanation I’ve managed to find anywhere on the net.
BTW, once you have imported the data with vlookup and are satisfied… hilight the entire columns with the lookup formula, COPY, then - Paste special- VALUES. This converts the formula to the displayed value.
This is good if the source data, the second spreadsheet, does not continuously update, if the lookup does not need to be refreshed regularly. Now you have everything in 1 sheet.
Alternatively, I like to copy the source data into a second tab in the first spreadsheet, then do the VLOOKUP stuff mentioned above. This way I don’t have to keep both sheets around as files, no hassles with paths, filenames, etc.
(More technical - As a warning - if someone shows you pretty pivot tables, be warned - tacking a column onto the sheet beside the pivot table but then refreshing so the table contents change, or sorting the table - the data beside will not stay with its original data. Do not mix data colums with pivot tables)
One thing to add to Bald Taco’s instructions, make sure you “anchor” your look up table range (box 2 in Bald’s info). Excel defaults to auto fill or auto-increment or whatever it is called, so when you drag down it adjusts the numbers to correspond to the new row. If your table isn’t sorted, it can cause problems because the first row the formula will be =vlookup(A1, sheet2!A1:B100, 2, FALSE) but when you drag it down it will be =vlookup(A1, Sheet2!A2:B101, 2, False). You need to anchor it with the dollar sign to be =vlookup(A1, Sheet2!A$1:B$100, 2, False) so it won’t change when you drag it down.