pivot table from two data tables

[ajm]

i am having trouble creating a pivot table from the consolidation of two data tables. i am using only 1 field from each data table in the pivot table. This field is common to both tables. its called Status.

so, its the row name and then i am trying to do a count of the same field. the end result should be a count of records from both data tables by their status.

I have tried two methods: the first is to create the pivot table off the first data table, add it to the data model by selecting the box at the bottom of the piv table wizard, then selecting All from the Pivot Table fields menu. I then select the Status field as Row and Status field again to count. This gives me a pivot table which correctly counts the records by status counts from the first data table. Then i select the Status field from the second data table in the Pivot Fields menu, adding it to rows and to data. when i add it to the Rows area, i get this below where it appears to include all statuses (from the data tables under each specific status (sort of like a sub status) from the first data table and gives the count of the records from the first data table status against each of the sub statuses. the 1564 is actually the number of approved records in the first data table.

what i want to see is a pivot table combining the counts of records for each status from each data table.

 

[GlennUK]

How are you joining the tables? What relationship did you make?

 

[ajm]

How are you joining the tables? What relationship did you make?

G'day Glenn. i actually didn't venture into the relationship between the two tables. i looked at it but didn't go further. it seems that the relationship between the two tables works the same as it would if ms access was being used and you link the tables. the two tables i am using represents a Library (sharepoint) of documents where a library can only have 5000 records. the total record set is currently closer to 9000 records so we now have 2 Libraries. both libraries contain the same fields.

I was hoping to use the consolidated ranges method of the pivot table wizard to rejoin the two sets of documents. exactly as if I copied the second set directly under the first set on a tab and then pivoted off that.

 

[GlennUK]

If you are not going to join the tables then that is the result you are going to get. But if you only want such a simple result you don't have to join tables, just do 2 pivottables, one for each set of data, and then have a manually created results area and add the results, using GETPIVOTDATA twice, added, in formulas, once for each pivottable.

 

[ajm]

If you are not going to join the tables then that is the result you are going to get. But if you only want such a simple result you don't have to join tables, just do 2 pivottables, one for each set of data, and then have a manually created results area and add the results, using GETPIVOTDATA twice, added, in formulas, once for each pivottable.

cheers glenn. have been doing just that but thought i would try the consolidated ranges method as i haven't used it before. it does seem a shame that you can't use it to create an appended range.

 

[GlennUK]

I'm not sure you were doing things the right way. Have a read of this from Debra Dalgleish, who knows a thing or two about PivotTables (MS MVP for a long time): Excel Pivot Table Multiple Consolidation Ranges