Bills of Materials - Total Quantities required Help

[PivotIdiot]

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

[TABLE="width: 821"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Level[/TD]
[TD]Part Number[/TD]
[TD]REV[/TD]
[TD]Qty Per[/TD]
[TD]Qty Total[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]HD IBC 7500 F50T[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]FHF57 AM132/M[/TD]
[TD]01[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]IBC 0000-0006[/TD]
[TD]01[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]IBC 0000-0007[/TD]
[TD]01[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]IBC 0000-0600[/TD]
[TD]01[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4.1[/TD]
[TD]2[/TD]
[TD]IBC 0000-0601[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4.2[/TD]
[TD]2[/TD]
[TD]IBC 0000-0602[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4.3[/TD]
[TD]2[/TD]
[TD]4049-202[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]4.3.1[/TD]
[TD]3[/TD]
[TD]4049-202 A[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]4.3.2[/TD]
[TD]3[/TD]
[TD]4049-202 G[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]4.3.3[/TD]
[TD]3[/TD]
[TD]4049-202 B[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]4.3.4[/TD]
[TD]3[/TD]
[TD]4049-202 C[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]4.3.5[/TD]
[TD]3[/TD]
[TD]4049-202 E[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]4.3.6[/TD]
[TD]3[/TD]
[TD]4049-202 Arm[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[szita2000]

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

	A	B	C	D	E	F	G
Main Category	Sub 1	Sub 2	Sub 3	Sub 4	Unique identifier

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11112[/TD]

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

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=(A2&B2&C2&D2&E2)*1[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]=(A3&B3&C3&D3&E3)*1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]