Lookup values from a pivot table and mutiply them with their match from another sheet.

[PaulAllen]

Hi guys,

I would like to lookup specific values like A01 (horizontal) and 0-1 (vertical) from a Pivot table and multiply the value such as 0.6 with their matching value from another table (December 2016, 0-1 "value") . I tried to illustrate the data in the attached excel spreadsheet. The "RESULT" table is the preferred result (red values). The formula should be applied to a large set of data, so that I could adjust the selected months.

Thanks for your time and help.

[avk]

As per your file, your original data & required data (figures) is mismatch. Can you explain how comes those value. What is criteria. (Refer Dec17 & Oct17 values)

[sandy666]

So, which CODE is correct and where?

[PaulAllen]

Sorry for the confusion. I would like to get the sum of the data if it matches with the related figure of the pivot table. So the product code is A01 and it is linked to the other code 0-2,0-3 and 0-4 but not for the rest like 0-1 and 0-5. Then the related figure of for e.g. 0-2 (which is 0.6) will get multiplied with the matching code 0-2 of the second spreadsheet (Data, which is 20) and so on for the other values and its related matches. The goal is to get a sum of all the 0-xxxx where A01 is part of and get a sum for every month.

See new attachment.

[sandy666]

What about this?

Please Login or Register  to view this content.

[PaulAllen]

This would be perfect. Is it a Pivot-table which includes some functions?

[sandy666]

here is attachment, but your description still is not clear to me

[PaulAllen]

Thanks, I could somehow work with it but my goal is to create a table with lots of data and to calculate only the sum of the amount in which the product code ( like A01) is a part of like 0.6 for 0-2. It might be easier to say that 0-1 is the finished product and A01 is an ingredient. The table should show how much of A01 was used for every month in all the finished product it is part of. The if function could do the matching but It would create a lot of rows and data which are not important like if A01 is not in the finished product xy.

Thanks again for your time and support!

[sandy666]

Would be fine if you can create manually example xlsx to show how you want to see result for A01 and A02. E.g. full table for 2017. I do not think it is possible to multiply A01 0-1 and A02 0-1 by values from the main table in the same time and place it in the same place so I did it with different tables. It is initial step of course

[PaulAllen]

ok, I'm not sure whether a lookup or match function with some if conditions might be able to calculate the sums for each product code. I might need to use your if formulas as a first step and calculate the sum for every product code from that or do you think that there is somehow a way to create one table for all codes from the pivot table and the data table?

[sandy666]

How you got

Please Login or Register  to view this content.

Dec A01 = 22, =I$3*SUM(B3:B7)
but the rest? I can't find a reproducible method
How you count 10, 16 or 38, 6, 36 ?

[avk]

I think data structure not as per rules. Needs to changes in structure with all releated respect. No continuity in data.

[sandy666]

@avk
Do you think the results are from the ceiling? (random)

[bebo021999]

In condition that the code list between Pivot sheet and Data sheet is exactly the same in order: 0-1,0-2,...

See attachment.

[sandy666]

bebo, do you think it was OP mistake with the RESULT D2 and J3 ?

[bebo021999]

bebo, do you think it was OP mistake with the RESULT D2 and J3 ?

which file? there are several versions...

[sandy666]

Reslut tab #10 where result was produced by OP

[bebo021999]

bebo, do you think it was OP mistake with the RESULT D2 and J3 ?

Maybe, just guess... LOL

[sandy666]

we will see after OP's comment

[avk]

Ref attach file. I have some modification with helper columns.
As per logic in code : A02 > May : is 55.5. [where as result mentioned is 49.]
plz clarify.

[PaulAllen]

Thanks for your help so far and sorry for the late reply.

I tried out your suggested approaches and would go with bebo's one as I don't want to create a new table. 55 was right.

However, I tried to convert it to my real excel spreadsheet but I always get #Value or #N/A. I tried a few ways but I must have missed something. My thought is that the match between the month might be missing although I included this one.

I attached a similar worksheet where I illustrated my problem (see results calculation for each array.