Create a new table from two updating worksheets under various conditions

Hi guys,

I would like to create a new table based on data from two worksheets. The first one (RAW DATA, see attachment) includes values such as 00-001 as the final product and below this row its components (S1, D2,...) are listed. However, sometimes another not important value (which is different to the format xx-xxx) like 70450 is included which is not relevant for the FINAL table. The second workbook included worksheet related to a specific month (e.g. Month1 ). It shows how much of the final product like 00-001 was produced in Month 1.

The aim is to create a new table, to show how much of the products 00-001,...... was produced and how much of their components were like S1, S6 (but not 70450) were used.

I'm not sure whether a Pivot table could be used or whether some if-functions could be used. My previous attempts couldn't match all the data based on the conditions.

I attached a sample workbook.

I hope someone could help me.

Cheers, Paul

the quantities come from the "Month 1" tab, column H.

I hope the OP is not going have a Sheet per Month rather entering the Monthly data into a single data sheet with Month as a field (column),

No idea on the components!

The OP needs to think carefully about data collection and worry less about "reporting" it

Ok, it seems that my explanation was a bit confusing. However, I managed to change the RAW DATA spreadsheet so that the components such as S6 wont't be in the same array as the final product like 00-001. (See attachment)

The aim is to create a final spreadsheet (FINAL) that sums up the values from the the values from the months. Please note that the sample is just an illustration and that my real spreadsheet includes 36 month, and 15000 rows (final products and its components).

The final table should show the monthly amount of each final product and link that to the right components like 0.8 % of S6 in product 00-001 to show the monthly usage of the components as well. My attempt including =SUMIF('Month 1'!B1:G6,'Month 1'!G1,'Month 1'!G1:G6) is not sufficient as it does not create a new table.

I hope this description is more clear.

As pointed out earlier, you should avoid having separate tabs for each month. As you have a date in the "Month" tabs then it is quite easy to summarise by those dates.

With large volumes of data there is a performance price to pay for continuing along the lines of monthly tabs.