Combineing two Excel files

Posted on 2011-04-29
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?

Question by:MAVSS
    LVL 17

    Assisted Solution

    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:

    Author Comment

    Thanks gtgloner. These are two fairly large sheets so how would the formula be setup to lookup across sheets?
    LVL 17

    Expert Comment

    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.
    LVL 41

    Accepted Solution

    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)


    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.


    LVL 24

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    728 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

    18 Experts available now in Live!

    Get 1:1 Help Now