PivotIdiot
Board Regular
- Joined
- Jul 8, 2010
- Messages
- 76
- Office Version
- 365
- Platform
- Windows
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: -
<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
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
Last edited: