• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum 2 fields from different table using pivot

Ford

New Member
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.
 

Attachments

  • Book1.xlsx
    78.5 KB · Views: 11
Thanks Chihiro, but how can i do this unique identifier? and what can you suggest as a solution instead of using pivot?
 
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.
 
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.
 

Attachments

  • Book1.xlsx
    115.7 KB · Views: 8
Back
Top