Pivot table with 2 different sheets

Question

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.

Answer 1

I may misunderstand where you are hitting a wall. Sounds like you may already know that you can create a Pivot Table using multiple source tables or sheets of data. And this works fine (but does require that columns be named or captioned the same in each data source, of course). And you can specify multiple filters as needed.

https://support.office.com/en-us/article/Consolidate-multiple-worksheets-into-one-PivotTable-report-3ae257d2-ca94-49ff-a481-e9fc8adeeeb5