Hi.
Long term browser, first time poster!
Wonder if anyone can give me a hand or point me in the right direction.
I have a workbook with several sheets of data, all of which is largely the same but has slightly different nuances (mainly column heading locations).
The information comes from different sources so it can't be manipulated, although I have made the headings the same.
I have created Pivot Tables for each sheet, but want to be able to have a master pivot table for the data on two particular sheets. I am not fussed if this means consolidating the data into one sheet, although I would like to automate this, rather than copy, paste and manipulate manually.
More details on the data;
Sheet One headings.
Date; Location; Item; Value; Method; Occurrence
Sheet two headings.
Date; Type; Description; Value; Item; Occurrence
I would like the master PT to have Item and Occurrence as the row filters and the table values to be a various outputs of the Value of the data.
I have tried a few methods; using the Data Model to link the two tables (doesn't work as there are duplicate values in each of the tables), consolidation of the data using the Data Tools (doesn't pull forward text entries), vlookup with the two pivot tables (seems to blow excel up).
I would prefer a solution that doesn't involve VBA or Access, but I am happy to explore Powerpivot if this is the way forward (I am using Excel 2013 btw).
I had a search but couldn't find anything that seemed to answer my question, so if I have overlooked an article or post feelfree to direct me there.
Thanks!!
Long term browser, first time poster!
Wonder if anyone can give me a hand or point me in the right direction.
I have a workbook with several sheets of data, all of which is largely the same but has slightly different nuances (mainly column heading locations).
The information comes from different sources so it can't be manipulated, although I have made the headings the same.
I have created Pivot Tables for each sheet, but want to be able to have a master pivot table for the data on two particular sheets. I am not fussed if this means consolidating the data into one sheet, although I would like to automate this, rather than copy, paste and manipulate manually.
More details on the data;
Sheet One headings.
Date; Location; Item; Value; Method; Occurrence
Sheet two headings.
Date; Type; Description; Value; Item; Occurrence
I would like the master PT to have Item and Occurrence as the row filters and the table values to be a various outputs of the Value of the data.
I have tried a few methods; using the Data Model to link the two tables (doesn't work as there are duplicate values in each of the tables), consolidation of the data using the Data Tools (doesn't pull forward text entries), vlookup with the two pivot tables (seems to blow excel up).
I would prefer a solution that doesn't involve VBA or Access, but I am happy to explore Powerpivot if this is the way forward (I am using Excel 2013 btw).
I had a search but couldn't find anything that seemed to answer my question, so if I have overlooked an article or post feelfree to direct me there.
Thanks!!
