Bills of Materials in manufacturing

padil110

New Member
Joined
Mar 22, 2019
Messages
6
Mr Excel,

I am trying to pull a dependent part number based on its assembly level in a bill of material. Here is the bill:

ABC
1Assembly LevelPart Numberdesired formula result
20CakeCake
31CandlesCake
41DoughCake
52eggsDough
62milkDough
72sugarDough
82flourDough
91Chocolate chipsCake
102ChocolateChocolate chips
111FrostingCake
122sugarFrosting
132creamFrosting
143milkcream
153buttercream

<colgroup><col width="65" style="width: 65pt;"><col width="104" style="width: 104pt;"><col width="82" style="width: 82pt;"><col width="114" style="width: 114pt;"></colgroup><tbody>
</tbody>
as you can see, the bill is arranged by level starting with the finished product (cake, at level 0). From there you can see that all the level 1 parts (candles, dough, etc.) are the main ingredients to build the cake. further you can see some levels 2's (eggs, milk, sugar, etc.) that are used to make the dough (level 1). Im trying to find a formula to search up to find the next vertical part number which would be the level value -1 (so if the part level is 3, search up for the next level 2, if the part level is 2, then search up to find the next level 1). for example, candles (level 1) would go into the finished part cake (level 0), and milk and butter (level 3's) would go into cream (level 2). I just want to display the results in the C2:15. Can you help me? Thanks so much
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,843
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Put this formula in C2.
Code:
=INDEX($B$2:B2,MAX(IF($A$2:A2=A2-1,ROW($A$2:A2)-MIN(ROW($A$2:A2))+1)))

It's an array formula so hit Ctl+Shift+Enter when entering the formula. Then copy down.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Another way, formula copied down, normally entered:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Assembly Level</td><td style=";">Part Number</td><td style=";">Formula result</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">0</td><td style=";">Cake</td><td style=";">Cake</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1</td><td style=";">Candles</td><td style=";">Cake</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">1</td><td style=";">Dough</td><td style=";">Cake</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">2</td><td style=";">eggs</td><td style=";">Dough</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">2</td><td style=";">milk</td><td style=";">Dough</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">2</td><td style=";">sugar</td><td style=";">Dough</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">2</td><td style=";">flour</td><td style=";">Dough</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">1</td><td style=";">Chocolate chips</td><td style=";">Cake</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">2</td><td style=";">Chocolate</td><td style=";">Chocolate chips</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">1</td><td style=";">Frosting</td><td style=";">Cake</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">2</td><td style=";">sugar</td><td style=";">Frosting</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">2</td><td style=";">cream</td><td style=";">Frosting</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">3</td><td style=";">milk</td><td style=";">cream</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">3</td><td style=";">butter</td><td style=";">cream</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet645</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IF(<font color="Blue">A2=0,B$2,LOOKUP(<font color="Red">2,1/(<font color="Green">A$2:A2=A2-1</font>),B$2:B2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

padil110

New Member
Joined
Mar 22, 2019
Messages
6
Put this formula in C2.
Code:
=INDEX($B$2:B2,MAX(IF($A$2:A2=A2-1,ROW($A$2:A2)-MIN(ROW($A$2:A2))+1)))

It's an array formula so hit Ctl+Shift+Enter when entering the formula. Then copy down.

Dude, it worked. Thank you very much. Now I'm going to try it with some of the hard core BOMs that i work with at work. thanks so much.
 

padil110

New Member
Joined
Mar 22, 2019
Messages
6

ADVERTISEMENT

Hi,

Another way, formula copied down, normally entered:

ABC
1Assembly LevelPart NumberFormula result
20CakeCake
31CandlesCake
41DoughCake
52eggsDough
62milkDough
72sugarDough
82flourDough
91Chocolate chipsCake
102ChocolateChocolate chips
111FrostingCake
122sugarFrosting
132creamFrosting
143milkcream
153buttercream

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet645

Worksheet Formulas
CellFormula
C2=IF(A2=0,B$2,LOOKUP(2,1/(A$2:A2=A2-1),B$2:B2))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

this one also worked. Thank you so much for taking the time to help me.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,843
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Glad that the solutions worked out for you. I would actually probably go with jtakw's formula, especially if you're dealing with a lot of data because I would imagine that it would probably perform better.
 

padil110

New Member
Joined
Mar 22, 2019
Messages
6
thanks again for your help, I have a new problem. I was wondering if you could help me out. I have created a query to extract bills of material form our ERP system. The bills for the full assembly are massive, up to 500 items. The problem is that they are not all exploded or linked in a parent/ child fashion. for instance, the bill for the top item will have the first level parent/ child, but then the child has its own bill which isn't indented in the first bill. Im trying to make a simple, refreshable, planning program to tell us what materials we need per the total bill of material (all levels). Here is what I'm talking about. column A and B are the way the query generates the data, all parent/ child bills are organized in 1-2 levels. I want to display C and D, which is a full linked bill of material. Its really tough, and i am struggling.

ABCD
1ParentChild1Car
2carwheels2wheels
3carcabin3rubber
4carmotor3rims
5carseats2cabin
6wheelsrubber3steering wheel
7wheelsrims3belts
8motorblock3dashboard
9motorpiston2motor
10motordistributor3block
11cabinsteering wheel4steel
12cabinbelts3piston
13cabindashboard4steel
14shirtfabric3distributor
15fabricwhool4rubber
16seatsleather4plastic
17seatscusion2seats
18pistonsteel3leather
19blocksteel3cusion
20distributorrubber1shirt
21distributorplastic2fabric

<colgroup><col width="27" style="width: 27pt;"><col width="65" style="width: 65pt;"><col width="89" style="width: 89pt;"><col width="65" style="width: 65pt;"><col width="81" style="width: 81pt;"></colgroup><tbody>
</tbody>

its nice that we have tables linked so we can make queries, but the system won't arrange the data like it should. If you could help me out, i would be most grateful.

thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,656
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top