zechariah
New Member
- Joined
- Oct 5, 2022
- Messages
- 5
- Office Version
- 2016
- 2013
- 2011
- 2010
- 2007
- Platform
- 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!
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,"") |