pivot table from two data tables

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,802
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.

1612754573980.png


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

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
How are you joining the tables? What relationship did you make?
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,802
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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

Well-known Member
Joined
Feb 5, 2003
Messages
1,802
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,808
Messages
5,627,010
Members
416,214
Latest member
boston814

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top