Is this for a mass upload, like you would typically do upon go live with the ERP system? The reason I ask is that after your up and running, most of your changes will just be to single level BOM's, not a fully indented top level.
If you're just uploading 1 subassembly and maybe a component has changed, you only would upload that assy part number, not all levels up, or all levels down. That would make populating that column much easier, but still not what you're looking for.
It is to bring our mass data up to date but also ongoing for each project. We build custom tailored machinery. Each time we build a new machine the BOM is loaded into the ERP system to generate purchase and work orders. A machine may have 200 or 1500 parts, and the number of new parts could be a handful to several hundred. The ERP system finds parts from the BOM that are not in its database and adds them before generating the internal and external paperwork.
Hi Jim, this should do what you want. Follow these steps.
- Export your bom with LEVELS.
- Open your export
- insert a blank column inbetween columns B and C
- in the new blank column "C", put a header in C1. I put mine as "Parent"
- in cell C2, put the following code
=IFERROR(IF(IFERROR(LEFT(A2,FIND("^^",SUBSTITUTE(A2,".","^^",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))-1), "error1") = "error1","Top Level",TRIM(VLOOKUP(LEFT(A2,FIND("^^",SUBSTITUTE(A2,".","^^",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))-1),A:B, 2, FALSE))), TRIM(VLOOKUP(VALUE(LEFT(A2,FIND("^^",SUBSTITUTE(A2,".","^^",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))-1)),A:B, 2, FALSE)))
Now do a fill down, so the entire column is filled with this code.
If you get "Top Level", then that item is associated to the top level. It's parent would be the original Part number you generated the BOM from.
Message was edited by: Greg Rupp