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

Item	Level	Part Number	REV	Qty Per	Qty Total
0	0	HD IBC 7500 F50T		2	2
1	1	FHF57 AM132/M	01	1	2
2	1	IBC 0000-0006	01	1	2
3	1	IBC 0000-0007	01	1	2
4	1	IBC 0000-0600	01	1	2
4.1	2	IBC 0000-0601		1	2
4.2	2	IBC 0000-0602		2	4
4.3	2	4049-202		4	8
4.3.1	3	4049-202 A		1	8
4.3.2	3	4049-202 G		2	16
4.3.3	3	4049-202 B		1	8
4.3.4	3	4049-202 C		1	8
4.3.5	3	4049-202 E		1	8
4.3.6	3	4049-202 Arm		1	8

<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

 

[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
1	Main Category	Sub 1	Sub 2	Sub 3	Sub 4		Unique identifier
2	1	1	1	1	2		11112
3	2	1	3	5			2135

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula G2 =(A2&B2&C2&D2&E2)*1 G3 =(A3&B3&C3&D3&E3)*1 <tbody> </tbody>

<tbody>
</tbody>