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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,434
Office Version
  1. 365
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
7,245
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Another way, formula copied down, normally entered:


Book1
ABC
1Assembly LevelPart NumberFormula result
20CakeCake
31CandlesCake
41DoughCake
52eggsDough
62milkDough
72sugarDough
82flourDough
91Chocolate chipsCake
102ChocolateChocolate chips
111FrostingCake
122sugarFrosting
132creamFrosting
143milkcream
153buttercream
Sheet645
Cell Formulas
RangeFormula
C2=IF(A2=0,B$2,LOOKUP(2,1/(A$2:A2=A2-1),B$2:B2))
 

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
3,434
Office Version
  1. 365
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.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, welcome to the forum.
 

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,975
Members
430,099
Latest member
rdhoy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top