Solved

Importing data into excel against one column

Posted on 2013-11-14
6
402 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
  • 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 80

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 31

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 80

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now