?
Solved

Combineing two Excel files

Posted on 2011-04-29
6
Medium Priority
?
192 Views
Last Modified: 2012-05-11
Hi Experts,

I have two separate files in Excel that I want to combine. The first file has data like below:

Name         Number    data2    data3
A                1234        yes       true
B                 2345       no         true
A                6789        no         false

The second file has data like below:

Type          Number    field1   field2
X               1234          text1   text2
Y               2345         text3    text4
U               6789         text5    text4


I need to combine the second file into the first file where the link is the "Number" field. So using the sample data above:

Number           Name         data2    data3       Type        field1   field2
1234               A                yes       true          X              text1   text2
2345               B                 no         true         Y              text3    text4
6789               A                no         false         U              text5    text4

What is the best way to do this? Macros? Formulas?

Thanks!
0
Comment
Question by:MAVSS
5 Comments
 
LVL 17

Assisted Solution

by:gtgloner
gtgloner earned 1000 total points
ID: 35492660
You can do it with 1. some re-arrangement of your tables and 2. the VLOOKUP sheet function. Attached is an example, have a look at the formulas in the yellow areas:
Book1.xls
0
 

Author Comment

by:MAVSS
ID: 35492901
Thanks gtgloner. These are two fairly large sheets so how would the formula be setup to lookup across sheets?
0
 
LVL 17

Expert Comment

by:gtgloner
ID: 35493013
I've tried that and got error messages. That's why I copied one table into the same sheet. I'll see if it's possible to do it the other way. You'll still have to re-arrange the tables so that the "Number" data is in the left-most column, that's the way VLOOKUP works.
0
 
LVL 42

Accepted Solution

by:
dlmille earned 1000 total points
ID: 35500215
This works just fine with index and match functions.

For example, for Type from File2, the formula in range D2 (assuming your data starts in range A1) would be:

=INDEX(array to search, MATCH(key,column of key in second file, exact match),first column for Type)

=INDEX('[File2.xlsx]Sheet1'!$A:$D,MATCH($B2,'[File2.xlsx]Sheet1'!$B:$B,0),1)

if you open file 1, just update links to update/get file2 data in.  Or, have both files open simultenously.

See attached.  Download the files to the same directory, then open them and the links created should work just fine.

Enjoy!

Dave
File1.xlsx
File2.xlsx
0
 
LVL 24

Expert Comment

by:Tracy
ID: 35877443
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

839 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