Advanced formula, basic MRP, Not sure if possible on excel.

[natheplas]

[TABLE="width: 1512"]
<colgroup><col><col><col><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Hi All,[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 12"]I've been tasked with making a very basic MRP and hope someone can help me automate it a bit.

Our products only go down one level, so I think it's doable, but the way I have made it is very
cumbersome and wondered if there is a formula I could use to help automate it. What I am currently entering takes hours.

I need to have a sum that looks at the productcode which is a component code. (240SSD)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Component Code[/TD]
[TD]Required Oct-2017[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]240 SSD[/TD]
[TD]??Required Sum??[/TD]
[/TR]
</tbody>[/TABLE]


The sum then looks at another work sheet to see what finished product that component goes into.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Component Code[/TD]
[TD]Finished Product[/TD]
[TD]Component Quantity in Finished Product[/TD]
[/TR]
[TR]
[TD]240 SSD[/TD]
[TD]Laptop[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]240 SSD[/TD]
[TD]Gaming PC[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]240SSD[/TD]
[TD]Mini PC[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Then it multiplies the component quantity in finished product * sales order demand (for that month) for the finished product.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Finished Product Code[/TD]
[TD]Oct 2017 Sales[/TD]
[TD]Nov 2017 Sales[/TD]
[/TR]
[TR]
[TD]Laptop[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Gaming PC[/TD]
[TD]10[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Mini PC[/TD]
[TD]5[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


So the final formula is something like

=(SHEETA!C2*SHEETB!B2)+(SHEETA!C3*SHEETB!B3)+(SHEETA!C4*SHEETB!C4)


This works for now, but it requires me going through and selecting each cell which takes hours, then if products are added or i miss one
there could easily be an error and I wouldn't know about.

Its a tall order, but if anyone knows a way I can join this up and automate it using an advanced vlook up or sumifs etc, that would be huge!

Thanks

Natheplas




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

 

[jarjarbingie]

[TABLE="width: 1512"]
<tbody>[TR]
[TD]Hi All,[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 12"]I've been tasked with making a very basic MRP and hope someone can help me automate it a bit.

Our products only go down one level, so I think it's doable, but the way I have made it is very
cumbersome and wondered if there is a formula I could use to help automate it. What I am currently entering takes hours.

I need to have a sum that looks at the productcode which is a component code. (240SSD)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Component Code[/TD]
[TD]Required Oct-2017[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]240 SSD[/TD]
[TD]??Required Sum??[/TD]
[/TR]
</tbody>[/TABLE]


The sum then looks at another work sheet to see what finished product that component goes into.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Component Code[/TD]
[TD]Finished Product[/TD]
[TD]Component Quantity in Finished Product[/TD]
[/TR]
[TR]
[TD]240 SSD[/TD]
[TD]Laptop[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]240 SSD[/TD]
[TD]Gaming PC[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]240SSD[/TD]
[TD]Mini PC[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Then it multiplies the component quantity in finished product * sales order demand (for that month) for the finished product.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Finished Product Code[/TD]
[TD]Oct 2017 Sales[/TD]
[TD]Nov 2017 Sales[/TD]
[/TR]
[TR]
[TD]Laptop[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Gaming PC[/TD]
[TD]10[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Mini PC[/TD]
[TD]5[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


So the final formula is something like

=(SHEETA!C2*SHEETB!B2)+(SHEETA!C3*SHEETB!B3)+(SHEETA!C4*SHEETB!C4)


This works for now, but it requires me going through and selecting each cell which takes hours, then if products are added or i miss one
there could easily be an error and I wouldn't know about.

Its a tall order, but if anyone knows a way I can join this up and automate it using an advanced vlook up or sumifs etc, that would be huge!

Thanks

Natheplas



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

B2=SUMPRODUCT(SheetB!$B$2:$B$4,OFFSET(SheetA!$C$1,MATCH($A2,SheetA!$A$2:$A$20,0),0,3,1))

this formula assumes that the data in Sheet A is grouped by Component Code and that the Products are listed in the same order as in Sheet B

 

[natheplas]

Hi jarjarbingie,

Thank you for the formula, but I'm just getting a '#VALUE!' is there anything you can do to see where I'm going wrong?

Can you send excels on this or copy parts of the worksheets over?

Now I know it can be automated, I would love to learn how.

Thanks.

 

[natheplas]

Hi,

Is there anyone that can help me with this please?

I'll send over anything people need or explain in more detail what I need the formula to do and how my current set up is.

Need some help going through why the above formula does not work and if it can be edited or a new formula that does work.

 

[Marcelo Branco]

Maybe this...

In the example below i've put all the tables in the same worksheet - adjust the ranges accordingly

Important: the values (headers) in B1 and C1 must be identical to those in J1:K1


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Component Code​

[/TD]
[TD]

Oct 2017​

[/TD]
[TD]

Nov 2017​

[/TD]
[TD][/TD]
[TD]

Component Code​

[/TD]
[TD]

Finished Product​

[/TD]
[TD]

Qty in Finished Product​

[/TD]
[TD][/TD]
[TD]

Product Code​

[/TD]
[TD]

Oct 2017​

[/TD]
[TD]

Nov 2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

240 SSD​

[/TD]
[TD]

26​

[/TD]
[TD]

22​

[/TD]
[TD][/TD]
[TD]

240 SSD​

[/TD]
[TD]

Laptop​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

Laptop​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

240 SSD​

[/TD]
[TD]

Gaming PC​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

Gaming PC​

[/TD]
[TD]

10​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

240 SSD​

[/TD]
[TD]

Mini PC​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

Mini PC​

[/TD]
[TD]

5​

[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

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


Array formula in B2 copied across to C2
=SUM(SUMIF($I$2:$I$10,IF($E$2:$E$10=$A2,$F$2:$F$10),INDEX($J$2:$Z$10,0,MATCH(B$1,$J$1:$Z$1,0)))*IF($E$2:$E$10=$A2,$G$2:$G$10))

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[natheplas]

Hi M,

Thank you for coming back to me.

=SUM(SUMIF('[Jens Mini MRP (version 2).xlsb]Report'!$A$8,IF(Table_Query_from_*****_Ltd3[STOCK_CODE]=ACTUAL!A11,Table_Query_from_*****_Ltd3[ASSEMBLY_CODE]),INDEX('Jens Mini MRP (version 2).xlsb'!Table3[Oct SO Demand],0,MATCH(Table1[[#Headers],[Oct SO Demand]],'Jens Mini MRP (version 2).xlsb'!Table3[[#Headers],[Oct SO Demand]],0)))*IF(Table_Query_from_*****_Ltd3[STOCK_CODE]=ACTUAL!A11,Table_Query_from_*****3[COMPONENT_QTY]))

When I used the formula your provided I get '22' for the first cell I tried it in. This is partly right as 22 is equal to finished product sales demand * component quantity, however, it is not picking up the lines underneath. It should be 22 + 90.

I also don't know how to apply this formula to the other cells. With a ctrl+shift+enter can you drag it down or do you have to do it a certain way for it to work?

I'm the closest I've been now, I just need it to look at the other rows on sheet 2 (E-G) and add them to the total, then work out how to apply that formula to all the cells in the column.

Thank you for your help! Hoping you can show me how to get to the finished line.

Natheplas

 

[natheplas]

Hi M,

I figured it out - I misunderstood part of your formula. It worked! Thank you so much for your help with this, I would of been totally stuck otherwise.

Regards,

Natheplas