Sum 2 fields from different table using pivot

[Ford]

Hi,

I'm new in excel pivot and wanted to know how to combine and sum the two fields from different tables or different files?

I attached the sample file and the red text color is the one that i need to combine and sum up the total qty with its corresponding correct commodity name, date machine no. etc.

I can't find in the forum the correct path on this so please help me to solve my problem as this is related to my job.

Thanks in advance.

 

[Chihiro]

You need unique identifier to construct one to many or one to one relationship. As is, your tables only have many to many relationship and can't be linked via pivot directly. It also requires Excel 2013 or later version.

Are you ok with solution other than Pivot?

Follow link in below thread for more detailed explanation of data model.
http://chandoo.org/forum/threads/multiple-tables-in-one-pivot-table.28183/#post-168731

 

[Ford]

Thanks Chihiro, but how can i do this unique identifier? and what can you suggest as a solution instead of using pivot?

 

[Chihiro]

Is data taken/exported from some sort of database? If so, I'm sure there's there is some foreign key associated to the table. Check to see if both tables share a foreign key and use that as unique ID.

If not, you can work around it by creating third table, list all unique values for Commodity (or other column that makes sense to build relationship on). And then assign unique value to each. Then use Index, Match or other formulas to tack that on to each table. Then construct your relationship.

Other solution is to extract unique values for the column that you want to summarize data on, and then do lookup calculations to build single table, combining information from both.

I'm going into meeting now, but if you have further questions, I'll check later today.

 

[Chihiro]

Actually, looking more closely at your table. It's much faster to combine it into single table. You are tracking all the same info on two separate table.

While you can still use Data Model, you will need to use MDX to combine two total columns, and that is going to be more work than just simple manual operation.

See attached for table structure.