Share via

Pivot Table in Excel - solution required !

Anonymous
2022-11-14T13:54:13+00:00

i have two data tables.. one (connected file) from ERP and another which is manual ( in which we have manual entries that are not yet incorporated in ERP)..

both table columns & column Heading are exactly same ..

i want to have report based on both tables in single pivot report.. i attempted to apply data model technique..

i have following challanges::

  • i am unable to apply calculated field (to sum or have difference of two columns in each table)
  • i am unable to accumulate balance (sum / difference) of those two calculated columns of each table..
  • although its showing 4 columns seperately but i need all summed up

although relationship auto detect works but on sum up and net figure i am unable to find solution.. please guide.

Microsoft 365 and Office | Excel | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 102K Reputation points Volunteer Moderator
    2022-11-15T00:03:21+00:00

    Hi,

    Share some data to work with, describe the question and show the expected result.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2022-11-14T21:06:50+00:00

    Hassann,

    If they are in fact tables, then you can use the relatively new function VSTACK. The below formula quite literally stacks your two tables to create a new array.

    =VSTACK(Table_1[#All],Table_2)

    Using a dynamic named range on the output of VSTACK will allow you to account for new rows when you refresh the pivot table. I would then implement any calculated fields into the combined pivot.

    Was this answer helpful?

    0 comments