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

[JDJohnson8783]

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:

Sheet 2 looks like this:

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.

 

[kweaver]

Posting a picture isn't any help when others try to use your data. Please use XL2BB to show the samples. It would also help if you could show a few expected results.

 

[JDJohnson8783]

Posting a picture isn't any help when others try to use your data. Please use XL2BB to show the samples. It would also help if you could show a few expected results.

Sorry about that..... I have completed what you asked. Please see below.

Here is sheet 1:

Copy of 230322-1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
5	Item No.	Part No.	Description				Qty.		UM		Dispo.
6	1	D-001-045-033	B & S Journal Bearing				1		EA		Repair
7	1.1	48270	Outer Shell				1		EA		Reuse
8	1.2	48200	Journal Pad Assembly				1		ST		Repair
9	1.2.1	48189	Journal Pad				4		EA		Repair
10	1.2.2	44928	Support Ball				5		EA		Reuse
11	1.3	B-025-045-054	Instrumented Journal Pad				1		EA		Repair
12	1.3.1	B-027-266	RTD (Single)				2		EA		New
13	1.4	48229	End Seal				1		EA		Reuse
14	1.5	48231	Floating Seal				1		EA		Reuse
15	1.6	05414	Support Shim				5		EA		Reuse
16	1.7	05413	Adjustment Shim				5		EA		New
17	1.8	M6x1.0 x 10mm Lg.	DIN 7984 S.H.C.S.				5		EA		New
18	1.9	M4x0.7 x 8mm Lg.	DIN 7984 S.H.C.S.				8		EA		New
19	1.10	26945	Pad Retainer Screw				5		EA		Reuse
20	1.11	93475A250	McMASTER Stainless Steel Washer				5		EA		New
21	1.12	M6x1.0 x 25mm Lg.	S.H.C.S.				2		EA		New
22	1.13	M6 x 24mm Lg.	DIN 9798 Taper Pin				2		EA		New
23
24
25
26
Engineering Release

Cells with Data Validation
Cell	Allow	Criteria
I6:J62	List	EA,ST
K6:K62	List	Modify,New,Repair,Reuse,Ref.

And Here is sheet 2:

Copy of 230322-1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	PartNo	Revision	Description	AltDescription1	AltDescription2	DescExtra	Quantity	IssueUM	ConsumptionConv	UM	Cost	Source	Drawing	Leadtime	Level	Location	Memo1	Memo2	Parent	Productline	Sequence	SortCode	Tag	Category	BomComplete	BomComments	Router
2	D-001-045-033.R		B & S Journal Bearing				1	EA		EA		M			0	HO
3	48270.U		Outer Shell				1	EA		EA		F			1	HO					100
4	48200.R		Journal Pad Assembly				1	ST		ST		F			1	HO					200
5	48189.R		Journal Pad				4	EA		EA		F			2	HO					300
6	44928.U		Support Ball				5	EA		EA		F			2	HO					400
7	B-025-045-054.R		Instrumented Journal Pad				1	EA		EA		F			1	HO					500
8	B-027-266.N		RTD (Single)				2	EA		EA		F			2	HO					600
9	48229.U		End Seal				1	EA		EA		F			1	HO					700
10	48231.U		Floating Seal				1	EA		EA		F			1	HO					800
11	05414.U		Support Shim				5	EA		EA		F			1	HO					900
12	05413.N		Adjustment Shim				5	EA		EA		F			1	HO					1000
13	M6x1.0 x 10mm Lg..N		DIN 7984 S.H.C.S.				5	EA		EA		F			1	HO					1100
14	M4x0.7 x 8mm Lg..N		DIN 7984 S.H.C.S.				8	EA		EA		F			1	HO					1200
15	26945.U		Pad Retainer Screw				5	EA		EA		F			2	HO					1300
16	93475A250.N		McMASTER Stainless Steel Washer				5	EA		EA		F			2	HO					1400
17	M6x1.0 x 25mm Lg..N		S.H.C.S.				2	EA		EA		F			2	HO					1500
18	M6 x 24mm Lg..N		DIN 9798 Taper Pin				2	EA		EA		F			2	HO					1600
19
20
21
BOMUploadRelease

Cell Formulas
Range	Formula
G2:G21	G2	=IF(ISBLANK('Engineering Release'!G6),"",'Engineering Release'!G6)
H2:I21	H2	=IF(ISBLANK('Engineering Release'!I6:J6),"",'Engineering Release'!I6:J6)
O2:O21	O2	=IF('Engineering Release'!A6<>"",LEN(SUBSTITUTE('Engineering Release'!A6,".",""))-1,"")
P2:P21	P2	=IF(ISBLANK('Engineering Release'!G6),"","HO")
A2:A21	A2	=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:F21	C2	=IF(ISBLANK('Engineering Release'!C6:F6), "", 'Engineering Release'!C6:F6)
J2:K21	J2	=IF(ISBLANK('Engineering Release'!I6:J6),"",'Engineering Release'!I6:J6)
L2:L21	L2	=IF(ISBLANK('Engineering Release'!G6),"",IF(O2=0, "M",IF(O2>0, "F","")))
S2:S18	S2	=LET(PART_ORIG,A2, PART,IFERROR(MID(PART_ORIG,1,FIND("-",PART_ORIG)-1),PART_ORIG), OUTLINE,'Engineering Release'!$A$6:$A$31, PARTS,'Engineering Release'!$C$6:$C$31, PARENT,IF(NOT(ISNUMBER(FIND(".",OUTLINE))), OUTLINE,LEFT(OUTLINE,LEN(OUTLINE)-2)), IDENTIFY_PARENT,XLOOKUP(PART,PARTS,PARENT,"",0), PARENT_PART,XLOOKUP(IDENTIFY_PARENT,OUTLINE,PARTS,"",0), TEXT(PARENT_PART,"0"))
S19:S21	S19	=IFERROR(XLOOKUP(O19-1,$O$2:$O19,$A$2:$A19,"",0,1),"")
U2:U21	U2	=IF(ISBLANK('Engineering Release'!C6),"",IF(O2=0,"",IF(U1="","100",IF(U1>100,U1+100,""))))
Dynamic array formulas.

There are 62 rows that employees can fill out all together on this form.

 

[JDJohnson8783]

Posting a picture isn't any help when others try to use your data. Please use XL2BB to show the samples. It would also help if you could show a few expected results.

should I repost this?

 

[kweaver]

I think I and other are trying to make sense out of this.

 

[Fluff]

Is this what you need

=XLOOKUP(TEXTBEFORE(A2,".",-1,,1),'Engineering Release'!$B$2:$B$18&"",'Engineering Release'!$A$2:$A$18)

 

[JDJohnson8783]

I think I and other are trying to make sense out of this.

So let me see if I can simplify it:
- Sheet 1 will autofill everything you see in sheet 2 that has data but it is missing the parent column being auto filled which is what I am looking to get a code to do.
- The parent column should populate with the parent part number to the child row.
- The top-level parent should not have anything in the parent column as it is the top parent.
- The part number in Column B Sheet 1 and the part number in Column A sheet 2 are the same except on sheet 2 the part number adds a "." and a letter to the end based off the Dispo. in column K sheet 1. This is for BOM/Router purposes to keep the different types of BOM's separate for each part number as each part number can be manufactured or repaired etc. and this designates the correct steps of work involved.
- The Item Number in Sheet 1 Column A is the outline that designates who the parent is of what child in the hierarchy. 1, 1.1, 1.2, 1.2.1, etc....
- formula in sheet 2 column S (Parent) is what I have tried and where I want the parent number to populate. I need to use column A on sheet 1 to determine who the parent is.

Please let me know if this helps.... I am desperate to get this to work. I appreciate any and all help I can get.

 

[JDJohnson8783]

Is this what you need

=XLOOKUP(TEXTBEFORE(A2,".",-1,,1),'Engineering Release'!$B$2:$B$18&"",'Engineering Release'!$A$2:$A$18)

That comes back with this error: "#NAME?"

 

[Fluff]

Ok, it sounds as though you do not have the latest updates which came out in January.
Are these the expected values

Fluff.xlsm
	A																		S
1	PartNo																		Parent
2	44928.U																		1.2.2
3	B-025-045-054.R																		1.3
4	B-027-266.N																		1.3.1
5	48229.U																		1.4
6	48231.U																		1.5
7	5414.U																		1.6
8	5413.N																		1.7
9	M6x1.0 x 10mm Lg..N																		1.8
10	M4x0.7 x 8mm Lg..N																		1.9
11	26945.U																		1.1
12	93475A250.N																		1.11
13	M6x1.0 x 25mm Lg..N																		1.12
14	M6 x 24mm Lg..N																		1.13
Sheet2

Cell Formulas
Range	Formula
A2:A14	A2	=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"), "")))))
S2:S14	S2	=XLOOKUP(TEXTBEFORE(A2,".",-1,,1),'Engineering Release'!$B$2:$B$18&"",'Engineering Release'!$A$2:$A$18)

 

[JDJohnson8783]

Ok, it sounds as though you do not have the latest updates which came out in January.
Are these the expected values

Fluff.xlsm
	A																		S
1	PartNo																		Parent
2	44928.U																		1.2.2
3	B-025-045-054.R																		1.3
4	B-027-266.N																		1.3.1
5	48229.U																		1.4
6	48231.U																		1.5
7	5414.U																		1.6
8	5413.N																		1.7
9	M6x1.0 x 10mm Lg..N																		1.8
10	M4x0.7 x 8mm Lg..N																		1.9
11	26945.U																		1.1
12	93475A250.N																		1.11
13	M6x1.0 x 25mm Lg..N																		1.12
14	M6 x 24mm Lg..N																		1.13
Sheet2

Cell Formulas
Range	Formula
A2:A14	A2	=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"), "")))))
S2:S14	S2	=XLOOKUP(TEXTBEFORE(A2,".",-1,,1),'Engineering Release'!$B$2:$B$18&"",'Engineering Release'!$A$2:$A$18)

The results should look like this:

Copy of 230322-1.xlsm
	A																		S
1	PartNo																		Parent
2	D-001-045-033.R
3	48270.U																		D-001-045-033.R
4	48200.R																		D-001-045-033.R
5	48189.R																		48200.R
6	44928.U																		48200.R
7	B-025-045-054.R																		D-001-045-033.R
8	B-027-266.N																		B-025-045-054.R
9	48229.U																		D-001-045-033.R
10	48231.U																		D-001-045-033.R
11	05414.U																		D-001-045-033.R
12	05413.N																		D-001-045-033.R
13	M6x1.0 x 10mm Lg..N																		D-001-045-033.R
14	M4x0.7 x 8mm Lg..N																		D-001-045-033.R
15	26945.U																		D-001-045-033.R
16	93475A250.N																		D-001-045-033.R
17	M6x1.0 x 25mm Lg..N																		D-001-045-033.R
18	M6 x 24mm Lg..N																		D-001-045-033.R
BOMUploadRelease

Cell Formulas
Range	Formula
S2:S14	S2	=IFERROR(XLOOKUP(O2-1,$O$2:$O2,$A$2:$A2,"",,-1),"")
A2:A18	A2	=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
	A	B
5	Item No.	Part No.
6	1	D-001-045-033
7	1.1	48270
8	1.2	48200
9	1.2.1	48189
10	1.2.2	44928
11	1.3	B-025-045-054
12	1.3.1	B-027-266
13	1.4	48229
14	1.5	48231
15	1.6	05414
16	1.7	05413
17	1.8	M6x1.0 x 10mm Lg.
18	1.9	M4x0.7 x 8mm Lg.
19	1.10	26945
20	1.11	93475A250
21	1.12	M6x1.0 x 25mm Lg.
22	1.13	M6 x 24mm Lg.
Engineering Release