Combineing two Excel files

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!
MAVSSAsked:
Who is Participating?
 
dlmilleCommented:
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
 
gtglonerCommented:
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
 
MAVSSAuthor Commented:
Thanks gtgloner. These are two fairly large sheets so how would the formula be setup to lookup across sheets?
0
 
gtglonerCommented:
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
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.