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.

click the Create Pivot button
click the Create Pivot button

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.

Inner Join Query
Inner Join Query

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.
______________

0 thoughts on “Combine Data From Two Excel Files in Pivot Table”

  1. KL
    I’m getting the error message “no columns available” when I validate the SQL statement
    Please advise.
    Thank you for your assistance.
    Jorge

  2. 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

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.