Excel Parent Child Relationship and Part Level

Any help is appreciated on this issue. I originally came here to get help on this and was able to find a solution that has worked up until now.

To reference the original post here is the link: Excel Formula Assigning Parent Part Number to a Child

Here is the link to the Excel Spreadsheet with the current codes being used: https://1drv.ms/x/s!Au53oWRKuRfD9kanfyQ62uW30r1V

Again, I will restate that it has worked up until now and I need to get a solution to fix the problem. So let me explain what is going on:

We start by entering in a Bill of Materials (BOM) information for parts on the first tab ("Engineering Release").

Column A (Item No.) is what is used to assign the parent part to a child part and determine what part level in the assembly it is. For example: Assembly = 1 is the assembly and its level is 0, Component 1 = 1.1 is the component part to 1 (assembly) and its level is 1, Sub-Component 1 = 1.1.1 is the sub-component part to 1.1 (component 1) and its level is 2, Component 2 = 1.2 is the component part to 1 (assembly) and its level is 1, Sub-Component 2 = 1.2.1 is the sub-component part to 1.2 (component 2) and its level is 2, Sub-Sub-Component = 1.2.1.1 is the sub-sub-component part to 1.2.1 (sub-component 2) and its level is 3, etc....

On the second tab ("BOMUploadRelease") I have everything from the first sheet automated with code to fill out a template sheet that gets uploaded to our ERP system.

In this sheet is where the codes are. The part number gets assigned a ".(letter)" added to the end of the part number from the first tab based off the selection of the "Dispo" Column on the first tab. The Level column on tab 2 ("BOMUploadRelease") is assigned based off Column A (Item No.) and the Parent column on tab 2 ("BOMUploadRelease") is assigned based off Column A (Item No.) but inputs the parent part number from the (PartNo) column on tab 2 ("BOMUploadRelease").

The first issue is the levels being assigned. The current code was working fine until we had a larger BOM and used double digits like 1.5.11 and due to the double digit "11" after the dot it gives it a level of 3 when it should still be a 2. Basically, for every "." in the string from Column A (Item No.) on the first tab ("Engineering Release") would be a level so 0 "."'s would be a level 0, 1 "." would be a level 1, 2 "."'s would be a level 2, 3 "."'s would be a level 3, etc.

The code being used for the part levels issue is:

=IF('Engineering Release'!A6<>"",LEN('Engineering Release'!A6)-LEN(SUBSTITUTE('Engineering Release'!A6,".","")),"")

For the second issue, it is the parent number being assigned incorrectly when there is the same subcomponent part number in the BOM but 2 different parent component numbers that use the same subcomponent part number. It will always pull the first parent part number for all the subcomponents rather then assigning then to the correct one.

What it looks like now:

What it should be with the corrected errors in red text:

The code being used for the assignment of the parent part number is:

=LET(x, XLOOKUP(TEXTBEFORE(A2,".",-1),
    'Engineering Release'!$B$6:$B$62,
    'Engineering Release'!$A$6:$A$62),
 y, 'Engineering Release'!$K$6:$K$62,IFERROR( XLOOKUP(TEXTBEFORE(x,".",-1),
             TEXT('Engineering Release'!$A$6:$A$62,"@"),
             'Engineering Release'!$B$6:$B$62 &"." &IFS (y="Ref.","REF", y="Repair","R", y="Reuse","U", y="Modify","M", y="New","N", y="Outsource","O")),""))          

Any and all help is appreciated and wanted. Please refer to the spreadsheet in the link, the pictures showing how it is not vs how it should be, and the codes being used.

You could use the following formula for Level in O2:

=LET(a,DROP(TOCOL('Engineering Release'!A:A,1),4),LEN(a)-LEN(SUBSTITUTE(a,".",)))

This spills the size of the number of parts used in the Engineering Release, so make sure to delete all values (formulas) from the cells below.

Then for Parent in S2 you could use:

=XLOOKUP(O2-1,O$1:O1,A$1:A1,"",,-1)

A bit off-topic:

You could simplify your formula in BomUploadRelease!A2 drastically:

old formula: =IF('Engineering Release'!K6:K6="Repair",CONCATENATE('Engineering Release'!B6:B6,".R"),IF('Engineering Release'!K6:K6="Reuse",CONCATENATE('Engineering Release'!B6:B6,".U"),IF('Engineering Release'!K6:K6="Modify",CONCATENATE('Engineering Release'!B6:B6,".M"),IF('Engineering Release'!K6:K6="Ref.",CONCATENATE('Engineering Release'!B6:B6,".F"),IF('Engineering Release'!K6:K6="New",CONCATENATE('Engineering Release'!B6:B6,".N"), "")))))

new formula: =DROP(TOCOL('Engineering Release'!B:B,1),1)&"."&SWITCH(DROP(TOCOL('Engineering Release'!K:K,1),1),"Repair","R","Reuse","U","Modify","M","Ref.","F","New","N")

Or Beta: =DROP('Engineering Release'!B:.B,5)&"."&SWITCH(DROP('Engineering Release'!K:.K,5),"Repair","R","Reuse","U","Modify","M","Ref.","F","New","N")

And your formula in BomUploadRelease!C2:

old formula: =IF(ISBLANK('Engineering Release'!C6:F6), "", 'Engineering Release'!C6:F6)

new (I don't see why you reference up to column F since these are merged and therefore won't contain data): =TOCOL(DROP('Engineering Release'!C:C,5),1)

or Beta: =DROP('Engineering Release'!C:.C,5)

on topic again: if you implemented the changes above, you could spill your Parent using:

=MAP(A2#,O2#,LAMBDA(PartNo,Level,XLOOKUP(Level-1,O2:Level,A2:PartNo,"",,-1)))