• 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.

Multiple Tables in one Pivot Table

CROW

New Member
I'm trying to find a way to reference data from 2 tables in one Pivot Table and not having much luck.

I have one table with scrap detail (SCRAP) and another table that has a summary of the production (PROD). I'd like to be able to show scrap as a percentage of production for a certain scrap reason and part number. See the tables below.

https://www.dropbox.com/s/kw9ycllwow3fz4m/Chandoo.xlsm?dl=0
 
One issue with your table(s). There is no unique identifier on either table.

In order to build relationships and combine 2 tables in a single pivot. You need one column in one of the table that has unique value in each row (i.e. no duplicates). Then that unique identifier replicated in second table (in second table, it can be duplicated).

For an example one table is list of customers and another table list of purchases.

Each customer is given unique ID (CustID), then in the table for purchases, you can associate multiple purchases to same CustID. Then you can summarize data based on CustID to show how much each customer has purchased etc.

But in your case there may be need for third table. That stores unique ID for each value that can occur in ADES1 column and replicate that on each of the tables you have. You can then use that to link tables.

Relationships will work as long as it's either one-to-one or one-to-many.

See links for detail on data model.
https://support.office.com/en-us/ar...ta-Model-533dc2b6-9288-4363-9538-8ea6e469112b

http://chandoo.org/wp/2013/07/01/introduction-to-excel-2013-data-model-relationships/
 
Back
Top