Bills of Materials in manufacturing

[padil110]

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:

[TABLE="width: 365"]
<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>[TR]
[TD="class: xl66, width: 65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"] [/TD]
[TD="class: xl66, width: 104, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]A[/TD]
[TD="class: xl66, width: 82, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]B[/TD]
[TD="class: xl66, width: 114, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]C[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]1[/TD]
[TD="class: xl64, align: center"]Assembly Level[/TD]
[TD="class: xl65"]Part Number[/TD]
[TD="class: xl65"]desired formula result[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]2[/TD]
[TD="class: xl64, align: center"]0[/TD]
[TD="class: xl65"]Cake[/TD]
[TD="class: xl65"]Cake[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]3[/TD]
[TD="class: xl64, align: center"]1[/TD]
[TD="class: xl65"]Candles[/TD]
[TD="class: xl65"]Cake[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]4[/TD]
[TD="class: xl64, align: center"]1[/TD]
[TD="class: xl65"]Dough[/TD]
[TD="class: xl65"]Cake[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]5[/TD]
[TD="class: xl64, align: center"]2[/TD]
[TD="class: xl65"]eggs[/TD]
[TD="class: xl65"]Dough[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]6[/TD]
[TD="class: xl64, align: center"]2[/TD]
[TD="class: xl65"]milk[/TD]
[TD="class: xl65"]Dough[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]7[/TD]
[TD="class: xl64, align: center"]2[/TD]
[TD="class: xl65"]sugar[/TD]
[TD="class: xl65"]Dough[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]8[/TD]
[TD="class: xl64, align: center"]2[/TD]
[TD="class: xl65"]flour[/TD]
[TD="class: xl65"]Dough[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]9[/TD]
[TD="class: xl64, align: center"]1[/TD]
[TD="class: xl65"]Chocolate chips[/TD]
[TD="class: xl65"]Cake[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]10[/TD]
[TD="class: xl64, align: center"]2[/TD]
[TD="class: xl65"]Chocolate[/TD]
[TD="class: xl65"]Chocolate chips[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]11[/TD]
[TD="class: xl64, align: center"]1[/TD]
[TD="class: xl65"]Frosting[/TD]
[TD="class: xl65"]Cake[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]12[/TD]
[TD="class: xl64, align: center"]2[/TD]
[TD="class: xl65"]sugar[/TD]
[TD="class: xl65"]Frosting[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]13[/TD]
[TD="class: xl64, align: center"]2[/TD]
[TD="class: xl65"]cream[/TD]
[TD="class: xl65"]Frosting[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]14[/TD]
[TD="class: xl64, align: center"]3[/TD]
[TD="class: xl65"]milk[/TD]
[TD="class: xl65"]cream[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]15[/TD]
[TD="class: xl64, align: center"]3[/TD]
[TD="class: xl65"]butter[/TD]
[TD="class: xl65"]cream[/TD]
[/TR]
</tbody>[/TABLE]
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

 

[lrobbo314]

Put this formula in C2.

=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]

Hi,

Another way, formula copied down, normally entered:

Book1
	A	B	C
1	Assembly Level	Part Number	Formula result
2	0	Cake	Cake
3	1	Candles	Cake
4	1	Dough	Cake
5	2	eggs	Dough
6	2	milk	Dough
7	2	sugar	Dough
8	2	flour	Dough
9	1	Chocolate chips	Cake
10	2	Chocolate	Chocolate chips
11	1	Frosting	Cake
12	2	sugar	Frosting
13	2	cream	Frosting
14	3	milk	cream
15	3	butter	cream
Sheet645

Cell Formulas
Range	Formula
C2	=IF(A2=0,B$2,LOOKUP(2,1/(A$2:A2=A2-1),B$2:B2))

 

[padil110]

Put this formula in C2.

=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]

Hi,

Another way, formula copied down, normally entered:

	A	B	C
Assembly Level	Part Number	Formula result
Cake	Cake
Candles	Cake
Dough	Cake
eggs	Dough
milk	Dough
sugar	Dough
flour	Dough
Chocolate chips	Cake
Chocolate	Chocolate chips
Frosting	Cake
sugar	Frosting
cream	Frosting
milk	cream
butter	cream

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet645​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(A2=0,B$2,LOOKUP(2,1/(A$2:A2=A2-1),B$2:B2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[lrobbo314]

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]

You're welcome, welcome to the forum.

 

[padil110]

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.

[TABLE="width: 327"]
<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>[TR]
[TD="class: xl63, width: 27, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "] [/TD]
[TD="class: xl63, width: 65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]A[/TD]
[TD="class: xl63, width: 89, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]B[/TD]
[TD="class: xl63, width: 65, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]C[/TD]
[TD="class: xl63, width: 81, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]D[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]1[/TD]
[TD="class: xl64"]Parent[/TD]
[TD="class: xl64"]Child[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64"]Car[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]2[/TD]
[TD="class: xl64"]car[/TD]
[TD="class: xl64"]wheels[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64"]wheels[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]3[/TD]
[TD="class: xl64"]car[/TD]
[TD="class: xl64"]cabin[/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64"]rubber[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]4[/TD]
[TD="class: xl64"]car[/TD]
[TD="class: xl64"]motor[/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64"]rims[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]5[/TD]
[TD="class: xl64"]car[/TD]
[TD="class: xl64"]seats[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64"]cabin[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]6[/TD]
[TD="class: xl64"]wheels[/TD]
[TD="class: xl64"]rubber[/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64"]steering wheel[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]7[/TD]
[TD="class: xl64"]wheels[/TD]
[TD="class: xl64"]rims[/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64"]belts[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]8[/TD]
[TD="class: xl64"]motor[/TD]
[TD="class: xl64"]block[/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64"]dashboard[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]9[/TD]
[TD="class: xl64"]motor[/TD]
[TD="class: xl64"]piston[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64"]motor[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]10[/TD]
[TD="class: xl64"]motor[/TD]
[TD="class: xl64"]distributor[/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64"]block[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]11[/TD]
[TD="class: xl64"]cabin[/TD]
[TD="class: xl64"]steering wheel[/TD]
[TD="class: xl64, align: right"]4[/TD]
[TD="class: xl64"]steel[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]12[/TD]
[TD="class: xl64"]cabin[/TD]
[TD="class: xl64"]belts[/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64"]piston[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]13[/TD]
[TD="class: xl64"]cabin[/TD]
[TD="class: xl64"]dashboard[/TD]
[TD="class: xl64, align: right"]4[/TD]
[TD="class: xl64"]steel[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]14[/TD]
[TD="class: xl64"]shirt[/TD]
[TD="class: xl64"]fabric[/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64"]distributor[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]15[/TD]
[TD="class: xl64"]fabric[/TD]
[TD="class: xl64"]whool[/TD]
[TD="class: xl64, align: right"]4[/TD]
[TD="class: xl64"]rubber[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]16[/TD]
[TD="class: xl64"]seats[/TD]
[TD="class: xl64"]leather[/TD]
[TD="class: xl64, align: right"]4[/TD]
[TD="class: xl64"]plastic[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]17[/TD]
[TD="class: xl64"]seats[/TD]
[TD="class: xl64"]cusion[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64"]seats[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]18[/TD]
[TD="class: xl64"]piston[/TD]
[TD="class: xl64"]steel[/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64"]leather[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]19[/TD]
[TD="class: xl64"]block[/TD]
[TD="class: xl64"]steel[/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64"]cusion[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: right"]20[/TD]
[TD="class: xl64"]distributor[/TD]
[TD="class: xl64"]rubber[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64"]shirt[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]21[/TD]
[TD="class: xl64"]distributor[/TD]
[TD="class: xl64"]plastic[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64"]fabric[/TD]
[/TR]
</tbody>[/TABLE]

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