Combine Data From Two Excel Files in Pivot Table

On Monday, Excel MVP Kirill Lapin (aka KL) shared his macro to create a standard pivot table from multiple workbooks (as opposed to worksheets in the same workbook). I promised you a second pivot table macro, and here it is — in today’s example, Kirill combines data from a sales list and price list, stored in separate workbooks. The macro combines the data and calculates the selling price for each item, then creates a pivot table from the results.

Thanks, Kirill, for providing these two pivot table macros, and the powerful techniques they demonstrate for creating pivot tables from data in different locations.

The Report Workbook

Like the previous example, in this sample there is a main file named Report.xls, that contains Kirill’s pivot table code, and a button that runs the macro. When you open the file, enable the macros, then click the Create Pivot button.

pivotcalcfiles01

In the example, the orders are in the DB-Sales.xls file, and the price list is in the DB-Items.xls file. In each file there is an Item_ID column, and those will be connected in the macro.

pivotcalcfiles02

After you click the button, the macro creates a pivot table from the two data files, which are stored in the same folder. You can refresh the pivot table, to show the latest data in the source files, as long as all the files stay in their original location.

pivotcalcfiles03

The Inner Join Query

Kirill’s macro creates an inner join query to combine the data from the selected files and calculate the sales price. In the Connection Properties dialog box, you can see the SQL string for the inner join query, with the Units * Unit Price calculation. The two files are connected by the Item_ID field, so the item name and unit price for each order can be displayed.

pivotcalcfiles04

Download the Sample File

To see Kirill’s pivot table code, you can download the Pivot Table Join Calculation example. The zipped folder that contains the Report.xls file, and the two data files. Unzip the folder, and keep all the files in the same folder. When you open the Report.xls file, enable macros to run the code.

______________

You may also like...

5 Responses

  1. sam says:

    Debra,
    This is not a union, its a inner join

  2. Thanks Sam, I must have had Union on the brain that day! It’s fixed now.

  3. […] Thanks to Excel MVP, Kirill Lapin, for sharing this very helpful tip. You can see more of Kirill’s work in the Contextures Blog post on Combining Data from Two Excel Files in a Pivot Table. […]

  4. Goodman says:

    KL
    I’m getting the error message “no columns available” when I validate the SQL statement
    Please advise.

    Thank you for your assistance.

    Jorge

  5. Fernando Fajardo says:

    HI

    Cool but I would like to know if there is a good way to do something similar to join two or more identical tables on different Sql databases in the same Sql Servers, or different ODBC databases

    Cherrs

    Fernando

Leave a Reply to Create Pivot Table from Similar Files in PowerPivot Cancel reply

Your email address will not be published. Required fields are marked *