Bill of materials quantity

[JPCoelho29]

Hi, I'm not an excel expert and I need some guidance.

I need to create a table where I can see the the quantity of material requirements for the ordered quantity of products. I have two tables like these:

Orders Table
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Order number[/TD]
[TD="align: center"]Product Code[/TD]
[TD="align: center"]Ordered Quantity[/TD]
[/TR]
[TR]
[TD="align: center"]52098[/TD]
[TD="align: center"]2147561[/TD]
[TD="align: center"]15000[/TD]
[/TR]
[TR]
[TD="align: center"]52098[/TD]
[TD="align: center"]3387595[/TD]
[TD="align: center"]5000[/TD]
[/TR]
</tbody>[/TABLE]

Bill of Materials Table
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]

Product Code​

[/TD]
[TD="align: center"]SubProduct Code[/TD]
[TD="align: center"]SubProQuantity/Product[/TD]
[/TR]
[TR]
[TD="align: center"]

2147561​

[/TD]
[TD="align: center"]80603[/TD]
[TD="align: center"]0,5[/TD]
[/TR]
[TR]
[TD="align: center"]

2147561​

[/TD]
[TD="align: center"]23127[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]

2147561​

[/TD]
[TD="align: center"]11002[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]

3387595​

[/TD]
[TD="align: center"]80603[/TD]
[TD="align: center"]0,5[/TD]
[/TR]
[TR]
[TD="align: center"]

3387595​

[/TD]
[TD="align: center"]29034[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]

3387595​

[/TD]
[TD="align: center"]11015[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]












And the table that I want to get is something like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Order Number[/TD]
[TD]Product Code[/TD]
[TD]SubProduct Code[/TD]
[TD]SubProduct Total Quantity[/TD]
[/TR]
[TR]
[TD]52098[/TD]
[TD]

2147561​

[/TD]
[TD]

80603​

[/TD]
[TD]7500[/TD]
[/TR]
[TR]
[TD]52098[/TD]
[TD]

2147561​

[/TD]
[TD]

23127​

[/TD]
[TD]15000[/TD]
[/TR]
[TR]
[TD]52098[/TD]
[TD]

2147561​

[/TD]
[TD]

11002​

[/TD]
[TD]15000[/TD]
[/TR]
</tbody>[/TABLE]

Someone knows how can I achieve this result? Thanks in advance!

 

[Oeldere]

Book1
	E	F	G	H	I
5	Bill of Materials Table
6	Product Code		SubProduct Code	SubProQuantity/Product
7	2147561	21475611	80603	0,5
8	2147561	21475612	23127	1
9	2147561	21475613	11002	1
10	3387595	33875951	80603	0,5
11	3387595	33875952	29034	1
12	3387595	33875953	11015	2
13
14
15
16	Order number	Product Code	Ordered Quantity
17	52098	2147561	15000	80603	7500
18	52098	2147561	15000	23127	15000
19	52098	2147561	15000	11002	15000
20	52098	2147561	15000
21
Blad5

Cell Formulas
Range	Formula
F7	=E7&COUNTIF($E$7:$E7,$E7)
F8	=E8&COUNTIF($E$7:$E8,$E8)
F9	=E9&COUNTIF($E$7:$E9,$E9)
F10	=E10&COUNTIF($E$7:$E10,$E10)
F11	=E11&COUNTIF($E$7:$E11,$E11)
F12	=E12&COUNTIF($E$7:$E12,$E12)
H17	=IFERROR(VLOOKUP($F17&ROW()-16,$F$7:$H$12,COLUMN()-6,0),"")
H18	=IFERROR(VLOOKUP($F18&ROW()-16,$F$7:$H$12,COLUMN()-6,0),"")
H19	=IFERROR(VLOOKUP($F19&ROW()-16,$F$7:$H$12,COLUMN()-6,0),"")
H20	=IFERROR(VLOOKUP($F20&ROW()-16,$F$7:$H$12,COLUMN()-6,0),"")
I17	=IFERROR(VLOOKUP($F17&ROW()-16,$F$7:$H$12,COLUMN()-6,0)*G17,"")
I18	=IFERROR(VLOOKUP($F18&ROW()-16,$F$7:$H$12,COLUMN()-6,0)*G18,"")
I19	=IFERROR(VLOOKUP($F19&ROW()-16,$F$7:$H$12,COLUMN()-6,0)*G19,"")
I20	=IFERROR(VLOOKUP($F20&ROW()-16,$F$7:$H$12,COLUMN()-6,0)*G20,"")

 

[JPCoelho29]

Oeldere, thanks for your quick response! The method that you proposed doesn't solve my problem, because you are asssuming that you have the first three columns of the third table. I need something that can join the information of the first two tables and get the third one. I think I need something like a multi vlookup function...

Thanks anyway