Parent-Child Relationships for a BOM

zechariah

New Member
Joined
Oct 5, 2022
Messages
5
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
ABCD
21 
31Ax
4 
5
6
7 
81Bx
9 
102 
112Ax
122Bx
132Cx
14 
153 
163Ax
17 
183Bx
19 
20 
214 
224Ax
234Bx
Sheet1
Cell Formulas
RangeFormula
B2B2=IF(D2<>"",COUNTIF(#REF!,">0")+1,"")
B4,B19:B21,B17,B14:B15,B9:B10B4=IF(D4<>"",COUNTIF(B$2:$B3,">0")+1,"")
B7B7=IF(D7<>"",COUNTIF(B$2:$B4,">0")+1,"")
 

Attachments

  • Screen Shot 2022-10-05 at 9.18.41 PM.png
    Screen Shot 2022-10-05 at 9.18.41 PM.png
    72 KB · Views: 9

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Hey Bebo!

Wow! Thank you very much!!! I really appreciate it!
 
Upvote 0
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!
 
Upvote 0
If you know how the code works, you should find it easy to twist.
Code:
=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"
 
Upvote 0
Here is a shorter one that you could also try.

22 10 07.xlsm
ABCD
1
21
31.1x
4 
5 
6 
7 
81.2x
9 
102 
112.1x
122.2x
132.3x
14 
153 
163.1x
17 
183.2x
19 
20 
214 
224.1x
234.2x
Parent Child
Cell Formulas
RangeFormula
B3:B23B3=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
ABCD
1
21
31Ax
4 
5 
6 
7 
81Bx
9 
102 
112Ax
122Bx
132Cx
14 
153 
163Ax
17 
183Bx
19 
20 
214 
224Ax
234Bx
Parent Child
Cell Formulas
RangeFormula
B3:B23B3=IF(D3="","",LOOKUP(9^9,A$2:A2)&CHAR(65+COUNTIF(B$2:B2,LOOKUP(9^9,A$2:A2)&"*")))
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,312
Members
452,634
Latest member
cpostell

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