Solved

Importing data into excel against one column

Posted on 2013-11-14
6
439 Views
Last Modified: 2014-05-21
Hello,

I'm trying to achieve the following scenario. I'd like to import data into excel against another column. E.g. column one is a "unique identifier". Say, for example, each staff member has a staff ID #. I'd like to import other columns "against" this column. So, if I have two spreadsheets I'd basically like to import one against the other, against column one. I want the import to look at the first column on spreadsheet #1, and if it finds a matching "unique identifier/staff ID" on the other spreadsheet I would like to import data for the other columns from that spreadsheet. Ideally I'd love to know how to 1.) Replace all data in spreadsheet 1 with the data in sheet #2. 2.) Replace only missing data in spreadsheet 1 with spreadsheet 2 (leaving on spreadsheet 1 that data that is already there).

I hope you can understand the question. Thank you, experts! If any clarification is need please let me know.
0
Comment
Question by:grindmygears
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39649598
Upload the sample files. I will write up the vba macro for you.
0
 
LVL 81

Accepted Solution

by:
byundt earned 250 total points
ID: 39650363
A VLOOKUP formula will bring the desired data back as long as the lookup column is to the left of the data column.
=VLOOKUP(unique identifier, '[other workbook.xlsx]Sheet2'!$A:$Z,5,FALSE)

Match the unique identifier with column A in Sheet2 or other workbook.xlsx. Then return a value from column E on that same row.

If you want to copy the formula across and down (wiping out the original data on Sheet1), then you could use a variation on:
=VLOOKUP(unique identifier, '[other workbook.xlsx]Sheet2'!$A:$Z,COLUMN() + x,FALSE)
where x is a positive or negative number that represents the "offset" of columns in Sheet2 and similar columns in Sheet1. If Sheet2 column E data are being returned to column E on Sheet1, then x would be 0.

If you want to put the formula only in blank cells, then:
1.  Select the range containing blank cells
2.  Hit F5 and choose Special... from the resulting dialog
3.  Specify Blank cells from the resulting dialog
4.  Click in the formula bar and enter a formula that is appropriate for the top left cell in the selection.
5.  Hold the Control key down, then hit Enter. This will put the formula in all blank cells, making appropriate adjustments for relative addressing.
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 39650890
If the columns aren't set out as byundt is suggesting, ie ID column to far left and data columns to the right, you can also use a combination of INDEX and MATCH.

=INDEX(SourceDataArea,MATCH(ID#,ID_Column_in_source,0),ColumnReference)

Column Reference could be calculated using a MATCH as well if so required:

=MATCH(Destination_Header,Source_Header_Row,0)

You can set Source Data to only one column, then you don't need the Column Reference so you could have different formulae in each column with the source data being the relative column in the source data file.

For large data sets I have found that INDEX/MATCH recalculate quicker than VLOOKUP.

Thanks
Rob H
0
 
LVL 81

Expert Comment

by:byundt
ID: 39651149
Rob,
INDEX and MATCH is only faster if you cache the value from MATCH in an auxiliary column. In all other apples to apples situations, VLOOKUP has a slight speed advantage.

Jon von der Heyden did a pretty exhaustive study of the matter in http://www.excelguru.ca/forums/showthread.php?132-INDEX-MATCH-versus-VLOOKUP 

As you pointed out, INDEX and MATCH does have the advantage of being able to work with lookup column to left or right of data column. Some people have standardized on INDEX and MATCH for all lookup problems for this reason.

Brad
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question