Pivot tables based on certain conditions

[Roshan.Shakya]

Hi experts, gurus, MVPs and guests,

I am trying to write a code that compares the surplus/deficit of two pivot tables in my "Compare" worksheet. I tried with some codes but could only create a single pivot table but not all the table that is supposed to be. When I used For Loop, the code did not create Pivot Table at all. Also I would want to create all the tables with name - ID & "_Table".

I have attached the workbook with desired output in the "Compare" worksheet.

I haven't added code here. My understanding level on this matter is not that great so even if I present code here it wouldn't be that worthy. I appreciate your time and suggestions / help.

Thank you
Roshan Shakya

[Olly]

The quickest way, if your requirement and data are this simple, is to blend your data using Power Query (Get & Transform Data).

Format your source data as tables - I named them tblPurchase and tblIncome, respectively. Then you can use a query like this to blend the data:

Please Login or Register  to view this content.

Close and load to Data Model, then you can create pivot table(s) as required.

You can then add a Measure to your Data Model, to calculate the variance dynamically:

Please Login or Register  to view this content.

See sample workbook: xlf_1297276_simple.xlsx

If you needed to handle more complex calculations / filtering, then I'd recommend loading Income and Purchase data to separate tables in your data model, and creating a "People" dimension table, with relationships to both those fact tables....

See worked example: xlf_1297276_powerpivot.xlsx

[Roshan.Shakya]

Thank you Olly. I am not that accustomed to Power Query.

The report I have is little complicated and has multiple phases of automation, which I have done with VBA. This is the output after automation and is a phase of interpretation. I have set "Check" comment when certain condition is passed by the data. Now the problem that I have is that the "Check" comment is dynamic and changes with modification of condition. I use 2010 Excel at work and not sure if Power Query works on this version.

I would have liked to save the list of "Check" as an array and recall this when filtering in the Pivot Table. I admit that my knowledge to save in Array variable is still primitive. I would request for helping me to understand this process. However, the final users intend to have the individual Pivot Tables for each "Check" component from 2 Sources (of output).

I appreciate Olly for sharing this valuable information.

Kind regards
Roshan Shakya