Search code examples
excel-formulaparent-childlookupchildren

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

A B C D E F G H I J K
5 Item No. Part No. Description Qty. UM Dispo.
6 1 D-008-165-044 ROCKER BACK JOURNAL BEARING 1 EA Repair
7 1.2 152B2157 JOURNAL PAD 1 EA Repair
8 1.2.3 159H610 PAD SHIM 1 EA New
9 1.2.5 1/4-20 X 3/4 LG S.H.C.S. 3 EA New
10 1.3 152B2158 JOURNAL PAD 1 EA Repair
11 1.3.3 159H610 PAD SHIM 1 EA New
12 1.3.5 1/4-20 X 3/4 LG S.H.C.S. 3 EA New
13 1.4 D-027-165-047 JOURNAL PAD MODIFICATION 1 EA Repair
14 1.4.3 159H610 PAD SHIM 1 EA New
15 1.4.5 1/4-20 X 3/4 LG S.H.C.S. 3 EA New
16 1.4.6 1/8 FF-S PIPE NIPPLE 1 EA New
17 1.4.7 1/8 DD-S 90 DEG. ELBOW FEMALE 1 EA New
18 1.4.8 1/4 PTF PIPE PLUG 1 EA New
19 1.4.9 C-202 COMM'L 2 EA New
20 1.4.10 6-32 X 1/4 LG B.H.C.S. 2 EA New
21 1.4.11 B-026-155 THERMOCOUPLE 1 EA New
22 1.5 D-027-165-048 JOURNAL PAD MODIFICATION 1 EA New
23 1.5.3 159H610 PAD SHIM 1 EA New
24 1.5.5 1/4-20 X 3/4 LG S.H.C.S. 3 EA New
25 1.5.6 1/8 FF-S PIPE NIPPLE 1 EA New
26 1.5.7 1/8 DD-S 90 DEG. ELBOW FEMALE 1 EA New
27 1.5.8 1/4 PTF PIPE PLUG 1 EA New
28 1.5.9 C-202 COMM'L 2 EA New
29 1.5.10 6-32 X 1/4 LG B.H.C.S. 2 EA New
30 1.5.11 B-026-155 THERMOCOUPLE 1 EA New
31 1.6 159A601-1 FLOATING SEAL (FWD) 1 EA Repair
32 1.6.1 1/4-20 X 2 1/2 LG S.H.C.S. 2 EA New
33 1.6.2 3/16 DIA. X 1/2 LG DOWEL 2 EA New
34 1.6.3 5/16 DIA. X 1/2 LG DOWEL 1 EA New
35 1.7 159A601-2 FLOATING SEAL (AFT) 1 EA Repair
36 1.7.1 1/4-20 X 2 1/2 LG S.H.C.S. 2 EA New
37 1.7.2 3/16 DIA. X 1/2 LG DOWEL 2 EA New
38 1.7.3 5/16 DIA. X 1/2 LG DOWEL 1 EA New
39 1.8 D-031-194 END PLATE (FWD) 1 EA Repair
40 1.9 D-031-195 END PLATE (AFT) 1 EA Repair
41 1.1 COMM L WIRE CLIPS 8 EA New
42 1.11 #6-32 X 1/4 LG B.H.C.S. 8 EA New
43 1.12 159B1680 PIPE ASSEMBLY 2 EA New
44 1.13 1/2-13 X 1 3/4 LG S.H.C.S. 24 EA New
45 1.14 159H613-2 WASHER 8 EA New

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.

A C O S
1 PartNo Description Level Parent
2 D-008-165-044.R ROCKER BACK JOURNAL BEARING 0
3 152B2157.R JOURNAL PAD 1 D-008-165-044.R
4 159H610.N PAD SHIM 2 152B2157.R
5 1/4-20 X 3/4 LG.N S.H.C.S. 2 152B2157.R
6 152B2158.R JOURNAL PAD 1 D-008-165-044.R
7 159H610.N PAD SHIM 2 152B2157.R
8 1/4-20 X 3/4 LG.N S.H.C.S. 2 152B2157.R
9 D-027-165-047.R JOURNAL PAD MODIFICATION 1 D-008-165-044.R
10 159H610.N PAD SHIM 2 152B2157.R
11 1/4-20 X 3/4 LG.N S.H.C.S. 2 152B2157.R
12 1/8 FF-S.N PIPE NIPPLE 2 D-027-165-047.R
13 1/8 DD-S.N 90 DEG. ELBOW FEMALE 2 D-027-165-047.R
14 1/4 PTF.N PIPE PLUG 2 D-027-165-047.R
15 C-202.N COMM'L 2 D-027-165-047.R
16 6-32 X 1/4 LG.N B.H.C.S. 3 D-027-165-047.R
17 B-026-155.N THERMOCOUPLE 3 D-027-165-047.R
18 D-027-165-048.N JOURNAL PAD MODIFICATION 1 D-008-165-044.R
19 159H610.N PAD SHIM 2 152B2157.R
20 1/4-20 X 3/4 LG.N S.H.C.S. 2 152B2157.R
21 1/8 FF-S.N PIPE NIPPLE 2 D-027-165-047.R
22 1/8 DD-S.N 90 DEG. ELBOW FEMALE 2 D-027-165-047.R
23 1/4 PTF.N PIPE PLUG 2 D-027-165-047.R
24 C-202.N COMM'L 2 D-027-165-047.R
25 6-32 X 1/4 LG.N B.H.C.S. 3 D-027-165-047.R
26 B-026-155.N THERMOCOUPLE 3 D-027-165-047.R
27 159A601-1.R FLOATING SEAL (FWD) 1 D-008-165-044.R
28 1/4-20 X 2 1/2 LG.N S.H.C.S. 2 159A601-1.R
29 3/16 DIA. X 1/2 LG.N DOWEL 2 159A601-1.R
30 5/16 DIA. X 1/2 LG.N DOWEL 2 159A601-1.R
31 159A601-2.R FLOATING SEAL (AFT) 1 D-008-165-044.R
32 1/4-20 X 2 1/2 LG.N S.H.C.S. 2 159A601-1.R
33 3/16 DIA. X 1/2 LG.N DOWEL 2 159A601-1.R
34 5/16 DIA. X 1/2 LG.N DOWEL 2 159A601-1.R
35 D-031-194.R END PLATE (FWD) 1 D-008-165-044.R
36 D-031-195.R END PLATE (AFT) 1 D-008-165-044.R
37 COMM L.N WIRE CLIPS 1 D-008-165-044.R
38 #6-32 X 1/4 LG.N B.H.C.S. 2 D-008-165-044.R
39 159B1680.N PIPE ASSEMBLY 2 D-008-165-044.R
40 1/2-13 X 1 3/4 LG.N S.H.C.S. 2 D-008-165-044.R
41 159H613-2.N WASHER 2 D-008-165-044.R

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:

Looks Like with current code

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

Should look like

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.

Solution

  • 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)))