The Straight Dope

Go Back   Straight Dope Message Board > Main > General Questions

Reply
 
Thread Tools Display Modes
  #1  
Old 06-09-2004, 12:41 PM
Emilio Lizardo Emilio Lizardo is offline
Guest
 
Join Date: Oct 2001
Can I merge two different Excel workbooks with a common column?

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!
Reply With Quote
Advertisements  
  #2  
Old 06-09-2004, 01:38 PM
keno keno is offline
Guest
 
Join Date: Apr 2000
Yes. If the larger list is in order, use VLOOKUP to pull the clicker info. Otherwise, use INDEX(masterlist of #s,MATCH(name, masterlist of names,0),1)
Reply With Quote
  #3  
Old 06-09-2004, 04:38 PM
AHunter3 AHunter3 is offline
Charter Member
 
Join Date: Mar 1999
Location: NY (Manhattan) NY USA
Posts: 16,310
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.
Reply With Quote
  #4  
Old 06-09-2004, 06:23 PM
BaldTaco BaldTaco is offline
Guest
 
Join Date: May 2000
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
Reply With Quote
  #5  
Old 06-09-2004, 06:28 PM
Blake Blake is offline
Member
 
Join Date: Mar 2001
Posts: 10,207
The other solution is to import the two tables into Access, combine the data in Access and then export.

At least thatís how I would do it.
Reply With Quote
  #6  
Old 09-23-2011, 10:37 AM
Ali500 Ali500 is offline
Guest
 
Join Date: Sep 2011
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.

Thank you!
Reply With Quote
  #7  
Old 09-23-2011, 12:18 PM
md2000 md2000 is offline
Guest
 
Join Date: Feb 2009
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)

Last edited by md2000; 09-23-2011 at 12:20 PM..
Reply With Quote
  #8  
Old 09-23-2011, 10:57 PM
Spud Spud is online now
Guest
 
Join Date: Jul 1999
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.

Last edited by Spud; 09-23-2011 at 10:57 PM..
Reply With Quote
Reply



Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 06:10 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.

Send questions for Cecil Adams to: cecil@chicagoreader.com

Send comments about this website to: webmaster@straightdope.com

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Publishers - interested in subscribing to the Straight Dope?
Write to: sdsubscriptions@chicagoreader.com.

Copyright © 2013 Sun-Times Media, LLC.