Pivot table from multiple data sources

[ncjharris]

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!!

 

[Stamoulis]

Hi, if the followinfg works for you please let me know.
You have to create relationship between the tables you want to use for the Pivot Table. For more info visit: Advanced PivotTables: Combining Data from Multiple Sheets - Tuts+ Computer Skills Tutorial

 

[ncjharris]

Thanks Stamoulis,

I did start there previously, but the issue I have is that there are duplicated entries within both of the source data tables. Therefore when I try to set up the table relationships with the lookup values I get a message telling me that there are duplicate values and that at least one of the columns must have unique values to create a relationship.
My issue is that within the columns there are no unique values; the dates, amounts and text values are all used more than once.

Do you know of a way around this?

 

[Stamoulis]

Could you please attach a sample of your data(including some duplicated entries)?

 

[ncjharris]

Hi, thanks for your response.

Here is my data; I've cut the list for brevity sake.

The headings on both lists that are the ones I primarily want the PT to show data for are Occurrence, Item and Value.

Thanks in advance!!!

https://app.box.com/s/0e3wuqscxmiyn4qxfbt6

 

[Stamoulis]

Do you take your data from a database? if yes then in the query include the id of sale/order , or create a view table in your database and then make the connection .

 

[ncjharris]

Thanks.

Information does come from a database, but there is no id of the sale or order. I suppose I could add a manual entry column with a unique number in each sheet for each entry to facilitate that.
Can you expand on your second point; what do you mean by 'creating a view table'?

 

[Stamoulis]

at least in sql server you can create an imaginary table containing specific data from multiple tabels or specific data from one table. then export that view-table to excel and create your PT.

 

[ncjharris]

Its not an sql database that the info comes from, some is user generated info, some is html output, some is text files.

I will try the unique identifier approach and see if that helps and report back.

Thanks

 

[ncjharris]

I added a new column to each table with a unique Id. This allowed me to create a relationship between the two tables, but not much else.
If I create a PT from the two datasets then it reports them separately. For example I use 'Occurrence' as the Row Filter then it puts the output from Account two as a subset of Account one, rather than merging the two together. So effectively I have a table that shows reports one as a product of the other, rather than 'merging' the overall data.

Any other ideas?