Consolidate 2 pivot tables into 1

[dactor]

Hey guys!

I have two pivot tables:
PV1
PV2

Each has a different data source:
Sheet 1
Sheet 2

Each of these sheets has different headings ...

How can I create a 3rd PV3 from the first PV1 and PV2?

Regards

RS

 

[Fazza]

Hi

For normal Excel - I don't know anything about Power Pivot - the method is to define the combined dataset via SQL.The manual approach in normal Excel might be starting from a new workbook, ALT-D-P and choose external data option at the first step. Follow the wizard. At the last step, choose the option to edit in MS Query. Then you can define the SQL. Such as to join data from multiple tables or UNION ALL data from multiple tables.

If you want specific help on the SQL, please provide further information.

regards

 

[dactor]

Thanks Fazza, I guess it is more complicated than I thought. I am not an SQL or VBA knowledge guy and was hoping to find something easier.... I could probably get a consolidated table of raw data first then run a pivot table, the only issue is that I have to keep doing the consolidation manually as I dont know of any way Excel can auto-add records from different sheets to one sheet.

 

[Fazza]

If the data is set up with headers in row 1 & data immediately under, Excel can automatically take care of the extra records. No VBA is needed. The SQL is a one-off set up.

Again, to help with the SQL, further info is needed.

The SQL can reference the whole sheet (as the source data) & then Excel picks up all records.

To give the idea, it is something like below. If you google you should find examples.

regards

SELECT some fields
FROM [name of one sheet$]
UNION ALL
SELECT some different headed fields
FROM [other sheet name$]

 

[dactor]

this sounds easy! let me get you more details here:

SHEET 1

Headers:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]JOB #[/TD]
[TD]JOB NAME[/TD]
[TD]PM[/TD]
[TD]START DATE[/TD]
[TD]END DATE[/TD]
[TD]LOCATION[/TD]
[TD]AMOUNT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

SHEET 2

Headers:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]DAILY#[/TD]
[TD]STATUS[/TD]
[TD]CLASS[/TD]
[TD]DATE[/TD]
[TD]JOB #[/TD]
[TD]IPO[/TD]
[TD]DEP[/TD]
[TD]Daily AMOUNT[/TD]
[TD]PM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


as you can see I have the two tables in different sheets. I can make one pivot table per sheet, but I want to be able to make a pivot table that has both sheets headings together specially that JOB# and PM are common between both sheets and wish to have one pivot table to get a global look at the projects.

The first sheet is for placing the project with its parameters. The second sheet is projects activities where for each one row in sheet one you can have 100s in sheet two depending on the project activities. So my pivot will take for example the project amount and deduct the daily amount from it. (This one pivot table saves me the hassle of creating a new consolidated sheet every time i need a report. )

Thanks.

Regards

RS

 

[Fazza]

OK. I don't know the specific requirements - which are essential to define the dataset (and this feeds into the pivot table).

Some sample data would be best, though maybe a description of the output would do. Such as you want fields : JOB #, PM, AMOUNT and the daily amounts are times -1 to make them subtract.

IF that were the requirement, SQL might be

SELECT [JOB #], PM, AMOUNT
FROM [SHEET 1$]
UNION ALL
SELECT [JOB #], PM, -SUM([Daily Amount])
FROM [SHEET 2$]
GROUP BY [JOB #], PM

That adds the daily amounts before they go into the pivot table. If you needed to retain the daily amounts in the pivot table, instead use

SELECT [JOB #], PM, AMOUNT
FROM [SHEET 1$]
UNION ALL
SELECT [JOB #], PM, [Daily Amount]
FROM [SHEET 2$]



If you needed to categorise amounts as summary or daily, then add another field like

SELECT [JOB #], PM, AMOUNT, 'SUMMARY' AS CATEGORY
FROM [SHEET 1$]
UNION ALL
SELECT [JOB #], PM, -SUM([Daily Amount]), 'DAILY' AS CATEGORY
FROM [SHEET 2$]
GROUP BY [JOB #], PM

So, some more description please of the requirement. regards

 

[Leavton]

i am no excel expert, but what about vlookup the data into one table and then do a pivot table?

 

[jfm1237]

Assuming your job # is a unique identifier, can you use PowerPivot to create a relationship between the two data sets and then pivot table from there?

 

[dactor]

Well, how do I create the realationship with creating another middle table you mean?

 

[jfm1237]

Try this: Adding Value to PowerPivot Data in Excel 2010