# Bills of Materials in manufacturing

##### New Member
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

### 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
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
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))

##### New Member
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.

##### New Member

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

</tbody>
Sheet645

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

</tbody>

<tbody>
</tbody>

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

#### lrobbo314

##### Well-known Member
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
You're welcome, welcome to the forum.

##### New Member
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

Replies
1
Views
888
Replies
3
Views
600
Replies
1
Views
443
Replies
3
Views
541
Replies
5
Views
505

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.

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.

### Which adblocker are you using?

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

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