Solved

Importing data into excel against one column

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Read about achieving the basic levels of HRIS security in the workplace.
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

24 Experts available now in Live!

Get 1:1 Help Now