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:

	A	B	C
1	Assembly Level	Part Number	desired 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

<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

 

[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
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

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

Sheet645​

Worksheet Formulas Cell Formula 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]

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.

	A	B	C	D
1	Parent	Child	1	Car
2	car	wheels	2	wheels
3	car	cabin	3	rubber
4	car	motor	3	rims
5	car	seats	2	cabin
6	wheels	rubber	3	steering wheel
7	wheels	rims	3	belts
8	motor	block	3	dashboard
9	motor	piston	2	motor
10	motor	distributor	3	block
11	cabin	steering wheel	4	steel
12	cabin	belts	3	piston
13	cabin	dashboard	4	steel
14	shirt	fabric	3	distributor
15	fabric	whool	4	rubber
16	seats	leather	4	plastic
17	seats	cusion	2	seats
18	piston	steel	3	leather
19	block	steel	3	cusion
20	distributor	rubber	1	shirt
21	distributor	plastic	2	fabric

<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