Parent-Child Relationships for a BOM

[zechariah]

Hello,

I am seeking a solution for the following problem. Basically, I am trying to create an automatic parent-child relationship for a BOM I am working on. See attached image and mini-sheet.
Column A has the assembly numbers (1, 2, 3, 4, etc.)
Column D has the parts within each assembly (denoted by x).
Please note there can be multiple empty rows between parts as seen in assembly 1 and 3.

I am looking for a formula to drag down in Column B to create the child relationships to the parent assembly.
Essentially, for every row with an "x", each part of an assembly will be labeled (A, B, C, D, etc) for every assembly.
For example, as shown, assembly 3 has two x's, so it should be labeled 3A and 3B.
If I added another 5 x's below what is currently shown, it should be labeled 3C, 3D, 3E, 3F and 3G.
Maximum number of x's under an assembly to be 26 (A to Z)

I have tried using COUNTIFs to solve this problem, but I am unable to reset the count for each assembly.
I would like to handle this without the use of VBA or Macros, so I am hoping there are some built-in excel formulas that can work.

I appreciate your help in solving this. Thank you!

Book-TEMP.xlsx
	A	B	C	D
2	1
3		1A		x
4
5
6
7
8		1B		x
9
10	2
11		2A		x
12		2B		x
13		2C		x
14
15	3
16		3A		x
17
18		3B		x
19
20
21	4
22		4A		x
23		4B		x
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IF(D2<>"",COUNTIF(#REF!,">0")+1,"")
B4,B19:B21,B17,B14:B15,B9:B10	B4	=IF(D4<>"",COUNTIF(B$2:$B3,">0")+1,"")
B7	B7	=IF(D7<>"",COUNTIF(B$2:$B4,">0")+1,"")

 

[bebo021999]

Book1
	A	B	C	D
2	1
3		1A		x
4
5
6
7
8		1B		x
9
10	2
11		2A		x
12		2B		x
13		2C		x
14
15	3
16		3A		x
17
18		3B		x
19
20
21	4
22		4A		x
23		4B		x
Sheet3

Cell Formulas
Range	Formula
B3:B23	B3	=IF(D3<>"x","",LOOKUP(100000,$A$2:A3)&CHAR(SUMPRODUCT(($D$2:D3="x")*(ROW($D$2:D3)>=LOOKUP(2,1/($A$2:A3<>""),ROW($A$2:A3))))+64))

 

[zechariah]

Hey Bebo!

Wow! Thank you very much!!! I really appreciate it!

 

[zechariah]

Hi Bebo,

If possible, can you advise a formula to change the children to .1, .2, .3, etc. instead of the letters A,B,C, etc.
For example, 3A will be 3.1 and 3B will be 3.2, etc. Appreciate your help! Thanks!

 

[bebo021999]

If you know how the code works, you should find it easy to twist.

=IF(D3<>"x","",LOOKUP(100000,$A$2:A3)&"." & TEXT(SUMPRODUCT(($D$2:D3="x")*(ROW($D$2:D3)>=LOOKUP(2,1/($A$2:A3<>""),ROW($A$2:A3)))),"0"))

Basically with "1.1,1.2,..,1.9" (with 1 digit after "." delimiter) no need to use TEXT function (...,"0")
But, in case you want it to be: "1.01,1.02,..,1.10", use "00" instead of "0"

 

[zechariah]

Thanks Bebo! Appreciate the help!

 

[Peter_SSs]

Here is a shorter one that you could also try.

22 10 07.xlsm
	A	B	C	D
1
2	1
3		1.1		x
4
5
6
7
8		1.2		x
9
10	2
11		2.1		x
12		2.2		x
13		2.3		x
14
15	3
16		3.1		x
17
18		3.2		x
19
20
21	4
22		4.1		x
23		4.2		x
Parent Child

Cell Formulas
Range	Formula
B3:B23	B3	=IF(D3="","",LOOKUP(9^9,A$2:A2)&"."&(COUNTIF(B$2:B2,LOOKUP(9^9,A$2:A2)&".*")+1))

Or if you wanted the letters as originally asked.

22 10 07.xlsm
	A	B	C	D
1
2	1
3		1A		x
4
5
6
7
8		1B		x
9
10	2
11		2A		x
12		2B		x
13		2C		x
14
15	3
16		3A		x
17
18		3B		x
19
20
21	4
22		4A		x
23		4B		x
Parent Child

Cell Formulas
Range	Formula
B3:B23	B3	=IF(D3="","",LOOKUP(9^9,A$2:A2)&CHAR(65+COUNTIF(B$2:B2,LOOKUP(9^9,A$2:A2)&"*")))

 

[zechariah]

Thanks Peter! Appreciate it!

 

[Peter_SSs]

You're welcome.