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
Solved

Importing data into excel against one column

Posted on 2013-11-14
6
425 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 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Outlook Free & Paid Tools
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

808 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