Bills of Materials - Total Quantities required Help

PivotIdiot

Board Regular
Joined
Jul 8, 2010
Messages
61
Hello all,

I need to create a summerised parts list from a Bill of Material, ensuring that all items are properly accounted for as the BOM is multi-leveled.

A sample of the data: -

ItemLevelPart NumberREVQty PerQty Total
00HD IBC 7500 F50T22
11FHF57 AM132/M0112
21IBC 0000-00060112
31IBC 0000-00070112
41IBC 0000-06000112
4.12IBC 0000-060112
4.22IBC 0000-060224
4.324049-20248
4.3.134049-202 A18
4.3.234049-202 G216
4.3.334049-202 B18
4.3.434049-202 C18
4.3.534049-202 E18
4.3.634049-202 Arm18

<tbody>
</tbody>

I have managed to set a 'LEVEL' column that counts the 'dots' and gives me appropriate value:
=IF([@Item]=0,0,(LEN([@Item]) - LEN(SUBSTITUTE([@Item],".","")))+1)

By using the LEVEL value i can use LEFT, FIND,SUBSTITUTE to locate the parent of each line to multiply back up the assemblies to get the 'real' quantity of each.
Then I can use a SUMPRODUCT to find the total of like items in another table.

The problem i have is that the formula to do this for 7 levels of assemblies is massive and difficult to maintain:

=IF([@Level]=1,[@[QTY Per]]*MAIN,


IF([@Level]=2,[@[QTY Per]]*MAIN *
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-1)))-1),Table13[[Item]:[QTY Per]],5,FALSE)),


IF([@Level]=3,[@[QTY Per]]*MAIN*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-1)))-1),Table13[[Item]:[QTY Per]],5,FALSE))*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-2)))-1),Table13[[Item]:[QTY Per]],5,FALSE)),


IF([@Level]=4,[@[QTY Per]]*MAIN*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-1)))-1),Table13[[Item]:[QTY Per]],5,FALSE))*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-2)))-1),Table13[[Item]:[QTY Per]],5,FALSE))*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-3)))-1),Table13[[Item]:[QTY Per]],5,FALSE)),


IF([@Level]=5,[@[QTY Per]]*MAIN*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-1)))-1),Table13[[Item]:[QTY Per]],5,FALSE))*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-2)))-1),Table13[[Item]:[QTY Per]],5,FALSE))*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-3)))-1),Table13[[Item]:[QTY Per]],5,FALSE))*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-4)))-1),Table13[[Item]:[QTY Per]],5,FALSE)),


IF([@Level]=6,[@[QTY Per]]*MAIN*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-1)))-1),Table13[[Item]:[QTY Per]],5,FALSE))*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-2)))-1),Table13[[Item]:[QTY Per]],5,FALSE))*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-3)))-1),Table13[[Item]:[QTY Per]],5,FALSE))*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-4)))-1),Table13[[Item]:[QTY Per]],5,FALSE))*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-5)))-1),Table13[[Item]:[QTY Per]],5,FALSE)),


IF([@Level]=7,[@[QTY Per]]*MAIN*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-1)))-1),Table13[[Item]:[QTY Per]],5,FALSE))*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-2)))-1),Table13[[Item]:[QTY Per]],5,FALSE))*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-3)))-1),Table13[[Item]:[QTY Per]],5,FALSE))*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-4)))-1),Table13[[Item]:[QTY Per]],5,FALSE))*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-5)))-1),Table13[[Item]:[QTY Per]],5,FALSE))*
(VLOOKUP(LEFT([@Item],FIND("#",SUBSTITUTE([@Item],".","#",([@Level]-6)))-1),Table13[[Item]:[QTY Per]],5,FALSE)),


MAIN)))))



I presume that there is an easier way using VBA - can anyone offer some assistance to that end?

Thanks in advance for getting this far,

Piv
 
Last edited:

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

szita2000

Board Regular
Joined
Apr 25, 2012
Messages
65
Hi.

I used to do a "workaround" where I created "Unique identifiers" in a helper column in a similar situation.

Imagine your multi level numbers broke out in to columns. (A:E here)
Then you wound concatenate the numbers and multiply it with 1 to make it a value. (This would give your unique id)

This way a certain sub category can be interpreted as a unique value which you can index, vlookup etc...


ABCDEFG
1Main CategorySub 1Sub 2Sub 3Sub 4Unique identifier
21111211112
321352135

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
G2=(A2&B2&C2&D2&E2)*1
G3=(A3&B3&C3&D3&E3)*1

<tbody>
</tbody>

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,100,138
Messages
5,472,741
Members
406,834
Latest member
RahafKh

This Week's Hot Topics

Top