0
I have 2 different excel sheets
tab1
A B C D E
1
2
3
and table 2
T U V W X
1
2
3
Let's say in B and U columns we have the same kind of data for instance "name of owner" and in C and V we have "number of animals owned", in D and W we have "country of the animal". The oter data are not relevant for my pivot table but I still want to be able to find them if needed.
I want a single pivot table which uses tab 1 and 2. I want to have D (or W as they are the same type of data) as filter and to see for every "name of the owner" the "number of animals owned" according to tab1 in a column and in another column the "number of animals owned" ccording to tab2.
I tried to use the pivot table helper but I didn't manage to use filters and I could obtain a pivot table by "name of owner" only if I used the datasets with as a first column B and U.
Thank you.
Ive read this page already but it is not clear to me how to add filters and how to achieve what i want without unselecting the first column. – morg – 2016-02-10T06:35:04.193
Unfortunately, Excel's consolidated Pivots are not as flexible as standard Pivots with a single data source. Some of those limitations are reviewed here, along with some good suggestions. Using PowerQuery would be an excellent option: http://www.contextures.com/xlPivot08.html.
As another workaround that may work for you, you could setup a new column as the first column in your Pivot ranges, make this calculated so that it appears as the Name of the Owner followed by the Country, for example: "Morg (USA)". Then you could easily filter by Country using Label Filter > Contains ... "USA".