Populate column with the parent part number using an Excel formula.

J

JDJohnson8783

New Member
Joined
Jul 5, 2023
Messages
10
Office Version
  1. 365
Platform
  1. Windows
I need some help please. I am looking for a excel formula that will determine the parent of a child based off a number/period outline on sheet 1 (yellow column sheet 1 "Item No.") and take the part number of the parent (orange column sheet 1 or yellow column sheet 2) and populate a column with the parent part number (green column sheet 2).

Sheet 1 looks like this:

Screenshot 2023-07-05 150237.png


Sheet 2 looks like this:

Screenshot 2023-07-05 150315.png


I need the parent part number to be placed into the row of the child in the parent column. Ideally the parent part number will match the part number on sheet 2 rather than sheet 1. If it is easier to pull the parent part number from sheet 1 I can concatenate the last 2 characters that are in sheet 2.

Please let me know if you have any questions. Been working on this for 4 days.
 
Sort by date Sort by votes
JDJohnson8783 said:
The results should look like this:

Copy of 230322-1.xlsm
AS
1PartNoParent
2D-001-045-033.R 
348270.UD-001-045-033.R
448200.RD-001-045-033.R
548189.R48200.R
644928.U48200.R
7B-025-045-054.RD-001-045-033.R
8B-027-266.NB-025-045-054.R
948229.UD-001-045-033.R
1048231.UD-001-045-033.R
1105414.UD-001-045-033.R
1205413.ND-001-045-033.R
13M6x1.0 x 10mm Lg..ND-001-045-033.R
14M4x0.7 x 8mm Lg..ND-001-045-033.R
1526945.UD-001-045-033.R
1693475A250.ND-001-045-033.R
17M6x1.0 x 25mm Lg..ND-001-045-033.R
18M6 x 24mm Lg..ND-001-045-033.R
BOMUploadRelease
Cell Formulas
RangeFormula
S2:S14S2=IFERROR(XLOOKUP(O2-1,$O$2:$O2,$A$2:$A2,"",,-1),"")
A2:A18A2=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"), "")))))


Based off this outline.
Copy of 230322-1.xlsm
AB
5Item No.Part No.
61D-001-045-033
71.148270
81.248200
91.2.148189
101.2.244928
111.3B-025-045-054
121.3.1B-027-266
131.448229
141.548231
151.605414
161.705413
171.8M6x1.0 x 10mm Lg.
181.9M4x0.7 x 8mm Lg.
191.1026945
201.1193475A250
211.12M6x1.0 x 25mm Lg.
221.13M6 x 24mm Lg.
Engineering Release
Click to expand...
This code works but not correctly and goes off the levels on sheet 2 which doesn't tell you the parent rather than the outline in sheet 1 column A which specifies the parent:

=IFERROR(XLOOKUP(O2-1,$O$2:$O2,$A$2:$A2,"",,-1),"") (This is wrong)

I have also tried this:

As sheet 2 (BOMUploadRelease) doesn't have what the correct result should be, my understanding is that the parent of outline 5.1 is 5 and outline 5.2.1.1 is 5.2.1 (see column D). The solution formula is using this understanding.

Screenshot notes:

  • The left side is an equivalent to sheet 1 and right side equivalent to sheet 2.
  • Yellow highlight is for validation purposes
  • Green highlight is the result looking up column B
  • Gray highlight is the result looking up column C
Solution Screenshot

Only 3 ranges in the formula need to be replaced to be usable in your workbook:

  • H2 with the cell of the part in sheet 2
  • $A$2:$A$7 with the range of the outline column in sheet 1
  • $B$2:$B$7 with the range of Part No. in sheet 1
=LET(PART_ORIG,H2,
PART,IFERROR(MID(PART_ORIG,1,FIND("-",PART_ORIG)-1),PART_ORIG),
OUTLINE,$A$2:$A$7,
PARTS,$B$2:$B$7,
PARENT,IF(NOT(ISNUMBER(FIND(".",OUTLINE))),OUTLINE,LEFT(OUTLINE,LEN(OUTLINE)-2)),
IDENTIFY_PARENT,XLOOKUP(PART,PARTS,PARENT,"error",0),
PARENT_PART,XLOOKUP(IDENTIFY_PARENT,OUTLINE,PARTS,"error",0),
TEXT(PARENT_PART,"0"))

Explanation of formula:

  1. PART_ORIG allows reference to the cell once, making copy/pasting and updating the formula easy
  2. PART keeps everything to the left of the the first dash, e.g. 123b-R -> 123b, thus allowing exact match in column B
  3. OUTLINE same concept as #1
  4. PARTS same concept as #1
  5. PARENT changes column A to the result shown in column D by removing the last 2 characters if it has a period
  6. IDENTIFY_PARENT looks for the parent outline from #5 (see column D)
  7. PARENT_PART identifies the parent part
  8. last line ensures the result is in text format, even if it is actually formatted as a number. E.g. cell B3 is a number while B4 is a text. Without this change, if the parent part is 234, the result would align to the right.
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Is there any chance of getting your version of Office updated with the latest updates?
 
Upvote 0
Fluff said:
Is there any chance of getting your version of Office updated with the latest updates?
Click to expand...
I am on 365 enterprise version 2108. My employer has stopped updates and they release the updates as they see fit. I do not have control over this.
 
Upvote 0
Fluff said:
Is there any chance of getting your version of Office updated with the latest updates?
Click to expand...
I just talked to IT. I am downloading the new 365 apps through the website. This is bypassing corporate IT updates.
 
Upvote 0
JDJohnson8783 said:
I just talked to IT. I am downloading the new 365 apps through the website. This is bypassing corporate IT updates.
Click to expand...
If you do that then only you or anyone using the Web version of Excel will be able to use your spreadsheet.
I suspect that you also have data issues ie is 1 and 1.1 a number, 1.1.1 will be text.

Give this a try.

20230707 Get Parent JDJohnson8783.xlsx
ABCDEFGHIJKLMNOPQRS
1PartNoRevisionDescriptionAltDescription1AltDescription2DescExtraQuantityIssueUMConsumptionConvUMCostSourceDrawingLeadtimeLevelLocationMemo1Memo2Parent
2 D-001-045-033B & S Journal Bearing  EA EA    
3 48270Outer Shell  EA EA 0 D-001-045-033
4 48200Journal Pad Assembly  ST ST 0 D-001-045-033
5 48189Journal Pad   EA EA 1 48200
6 44928Support Ball  EA EA 1 48200
7 B-025-045-054Instrumented Journal Pad  EA EA 0 D-001-045-033
8 B-027-266RTD (Single)  EA EA 1 B-025-045-054
9 48229End Seal  EA EA 0 D-001-045-033
10 48231Floating Seal  EA EA 0 D-001-045-033
11 5414Support Shim  EA EA 0 D-001-045-033
12 5413Adjustment Shim  EA EA 0 D-001-045-033
13 M6x1.0 x 10mm Lg.DIN 7984 S.H.C.S.  EA EA 0 D-001-045-033
14 M4x0.7 x 8mm Lg.DIN 7984 S.H.C.S.  EA EA 0 D-001-045-033
15 26945Pad Retainer Screw  EA EA 0 D-001-045-033
16 93475A250McMASTER Stainless Steel Washer  EA EA 0 D-001-045-033
17 M6x1.0 x 25mm Lg.S.H.C.S.  EA EA 0 D-001-045-033
18 M6 x 24mm Lg.DIN 9798 Taper Pin  EA EA 0 D-001-045-033
BOMUploadRelease
Cell Formulas
RangeFormula
G2:G18G2=IF(ISBLANK('Engineering Release'!G6),"",'Engineering Release'!G6)
H2:I18H2=IF(ISBLANK('Engineering Release'!I6:J6),"",'Engineering Release'!I6:J6)
O2:O18O2=IF('Engineering Release'!A6<>"",LEN(SUBSTITUTE('Engineering Release'!A6,".",""))-1,"")
P2:P18P2=IF(ISBLANK('Engineering Release'!G6),"","HO")
A2:A18A2=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"), "")))))
C2:F18C2=IF(ISBLANK('Engineering Release'!C6:F6), "", 'Engineering Release'!C6:F6)
J2:K18J2=IF(ISBLANK('Engineering Release'!I6:J6),"",'Engineering Release'!I6:J6)
L2:L18L2=IF(ISBLANK('Engineering Release'!G6),"",IF(O2=0, "M",IF(O2>0, "F","")))
S2:S18S2=LET(ItemNo,XLOOKUP(C2,'Engineering Release'!$C$6:$C$22,'Engineering Release'!$B$6:$B$22,""), ParentItem,IFERROR(REPLACE(ItemNo, FIND("~",SUBSTITUTE(ItemNo,".","~",LEN(ItemNo)-LEN(SUBSTITUTE(ItemNo,".","")))), 3,""),ItemNo), IF(ParentItem=ItemNo,"",XLOOKUP(TRIM(ParentItem),TRIM('Engineering Release'!$B$6:$B$22),'Engineering Release'!$C$6:$C$22,"Not Found")))
Dynamic array formulas.
 
Upvote 0
Alex Blakenburg said:
If you do that then only you or anyone using the Web version of Excel will be able to use your spreadsheet.
I suspect that you also have data issues ie is 1 and 1.1 a number, 1.1.1 will be text.

Give this a try.

20230707 Get Parent JDJohnson8783.xlsx
ABCDEFGHIJKLMNOPQRS
1PartNoRevisionDescriptionAltDescription1AltDescription2DescExtraQuantityIssueUMConsumptionConvUMCostSourceDrawingLeadtimeLevelLocationMemo1Memo2Parent
2 D-001-045-033B & S Journal Bearing  EA EA    
3 48270Outer Shell  EA EA 0 D-001-045-033
4 48200Journal Pad Assembly  ST ST 0 D-001-045-033
5 48189Journal Pad   EA EA 1 48200
6 44928Support Ball  EA EA 1 48200
7 B-025-045-054Instrumented Journal Pad  EA EA 0 D-001-045-033
8 B-027-266RTD (Single)  EA EA 1 B-025-045-054
9 48229End Seal  EA EA 0 D-001-045-033
10 48231Floating Seal  EA EA 0 D-001-045-033
11 5414Support Shim  EA EA 0 D-001-045-033
12 5413Adjustment Shim  EA EA 0 D-001-045-033
13 M6x1.0 x 10mm Lg.DIN 7984 S.H.C.S.  EA EA 0 D-001-045-033
14 M4x0.7 x 8mm Lg.DIN 7984 S.H.C.S.  EA EA 0 D-001-045-033
15 26945Pad Retainer Screw  EA EA 0 D-001-045-033
16 93475A250McMASTER Stainless Steel Washer  EA EA 0 D-001-045-033
17 M6x1.0 x 25mm Lg.S.H.C.S.  EA EA 0 D-001-045-033
18 M6 x 24mm Lg.DIN 9798 Taper Pin  EA EA 0 D-001-045-033
BOMUploadRelease
Cell Formulas
RangeFormula
G2:G18G2=IF(ISBLANK('Engineering Release'!G6),"",'Engineering Release'!G6)
H2:I18H2=IF(ISBLANK('Engineering Release'!I6:J6),"",'Engineering Release'!I6:J6)
O2:O18O2=IF('Engineering Release'!A6<>"",LEN(SUBSTITUTE('Engineering Release'!A6,".",""))-1,"")
P2:P18P2=IF(ISBLANK('Engineering Release'!G6),"","HO")
A2:A18A2=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"), "")))))
C2:F18C2=IF(ISBLANK('Engineering Release'!C6:F6), "", 'Engineering Release'!C6:F6)
J2:K18J2=IF(ISBLANK('Engineering Release'!I6:J6),"",'Engineering Release'!I6:J6)
L2:L18L2=IF(ISBLANK('Engineering Release'!G6),"",IF(O2=0, "M",IF(O2>0, "F","")))
S2:S18S2=LET(ItemNo,XLOOKUP(C2,'Engineering Release'!$C$6:$C$22,'Engineering Release'!$B$6:$B$22,""), ParentItem,IFERROR(REPLACE(ItemNo, FIND("~",SUBSTITUTE(ItemNo,".","~",LEN(ItemNo)-LEN(SUBSTITUTE(ItemNo,".","")))), 3,""),ItemNo), IF(ParentItem=ItemNo,"",XLOOKUP(TRIM(ParentItem),TRIM('Engineering Release'!$B$6:$B$22),'Engineering Release'!$C$6:$C$22,"Not Found")))
Dynamic array formulas.
Click to expand...

This returns a blank in my excel sheet all the way down.

I have had the best luck with this code:

=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")),
""))

The only problem is that it puts blanks in some rows and I am not sure why but it almost works 100%.
Sorry since I updated Xl2bb isn't letting me copy and paste the table so here is a picture of the results with the code above:

Screenshot 2023-07-07 074633.png
 
Upvote 0
Give this a try:
(My sample data seemed to have lost the .N, .U etc that got added to your part no)


Excel Formula: 
=LET(PartNo, LEFT(A2,LEN(A2)-2),
          ItemNo,XLOOKUP(TRIM(PartNo),TRIM('Engineering Release'!$C$6:$C$22),'Engineering Release'!$B$6:$B$22,""),
          ParentItem,IFERROR(REPLACE(ItemNo,
                                   FIND("~",SUBSTITUTE(ItemNo,".","~",LEN(ItemNo)-LEN(SUBSTITUTE(ItemNo,".","")))),
                                   3,""),ItemNo),
           IF(ParentItem=ItemNo,"",XLOOKUP(TRIM(ParentItem),TRIM('Engineering Release'!$B$6:$B$22),'Engineering Release'!$C$6:$C$22,"Not Found")))
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Does Excel have anything like Smartsheet's Hierarchy (Parent/Child rows)?
Replies
3
Views
2K
Edgar_
Edgar_
H
  • Solved
Create list of last(family) names when parents have children in more than 1 class
Replies
3
Views
100
Peter_SSs
Peter_SSs
C
  • Solved
Re-arranging and Sorting Data using a VBA Macro
2
Replies
10
Views
498
Akuini
Akuini
P
  • Question Question
Dependencies & Connections
Replies
0
Views
153
Pestomania
P
S
  • Question Question
Organization Chart Formula
Replies
3
Views
683
ShogunStealth
S

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top