How do I combine two sheets into a single pivot ta

问题:

I am doing a big conversion of database objects from T-SQL to PL/SQL (i.e. Microsoft to Oracle). I've two sheets tracking them. One is for existing procedures that are being converted and the other is for new ones that I've had to create. They both have the following columns:

Object Name   
Object Type   
-- other details inc lines of code etc   
Conversion Status   
Phase of move

What I want to do is create a chart where I can effectively group both sets of data together, with a slicer to choose which phase to view. There are five phases, so ideally I could choose phase one and see that out of 28 items in both sheets, 25 are done, 2 are still being test and 1 isn't started. If it could show new v. existing that would be good, but not essential.

However, I'm not really clear on how to do this. I tried to create a data model and combine both sets of data, but it wanted a relationship between them. I tried to provide Phase of move, but as it's not unique Excel wouldn't have it.

What's my next step?

---

To explain what I'm after, here's a chart from the first data set:

Here's a chart from the second data set:

What I basically want is a pivot chart (or regular chart) which combines both of these sets of information.

The Phase option and the Transition State options are common across both sheets. If there are ten items completed, in phase 1, on sheet 1, and five items completed, in phase 1, on sheet 2, I want my pivot chart to display fifteen items completed for phase 1.

---

It seems @pnuts solution is what I want, but I'm still having trouble. This is my (new and combined) PivotTable:

I did these steps:

• CTRL, D, P on first sheet
• Multiple consolidation ranges / pivot table
• I will create the page fields
• I chose range J:M on the first sheet and then "add"
• I selected the other sheet and chose range C:F and then add (note, these are the same four columns, in same order, from both sheets).
• Page fields was left at 0.
• I chose Next.
• I chose to export to an existing worksheet.

It does all of its calculations and gives me this:

Obviously, this is incorrect. If I change count to sum, I get 0 for everything. Here is some sample data, based on my first sheet:

What on earth am I doing wrong?

Edit 3:

Additional info at @pnuts request:

回答1:

Create a pivot table from multiple consolidation ranges (eg see here) then pivot the resulting Table.

Edit based on data sample now provided

This is what I get:

though I charted the Table without bothering to pivot that.