Parent Child derivation

[JackNijssen]

Hi,

In below example in column A+B, i have hierarchy on the node level of the parent in column A and the child in column B. I would like to derive the text of each parent in column C, like below. Is there a formula for column C how i can derive this value?

Column A Column B desired output Column C with parent
1 AA
2 BB AA
3 CC BB
4 DD CC
5 EE DD
5 EEE DD
5 EEEE DD
5 EEEEE DD

Thanks a lot, Jack

 

[VBE313]

Create a column that says "1" or "0" if the level is 1 aka Parent Part.
To the left of that columns, put the sums of the child parts you want to sum.
to the right of the "1" or "0" column, put this foumula in

=IFERROR(IF(L1=1,SUM(K2:INDEX($L2:$L$10,MATCH(TRUE,($L2:$L$10=1),0)),-1),""),IF(L4=1,SUM(K2:INDEX($L2:$L$10,MATCH(TRUE,($L2:$L$10=1),0)),-1),""))

Press Ctrl+Shift+Enter when entering the formula

In this example,

Column L is the "1" or "0" column
Column K is the sum of the child parts
Column M is where you enter this formula

If you need to sum up "Make" components that roll up to "Top Level"/Parent Parts, Then message me and I can show you what I have been working on for the past few months on how to accomplish this.

Thanks,

 

[JackNijssen]

Create a column that says "1" or "0" if the level is 1 aka Parent Part.
To the left of that columns, put the sums of the child parts you want to sum.
to the right of the "1" or "0" column, put this foumula in

=IFERROR(IF(L1=1,SUM(K2:INDEX($L2:$L$10,MATCH(TRUE,($L2:$L$10=1),0)),-1),""),IF(L4=1,SUM(K2:INDEX($L2:$L$10,MATCH(TRUE,($L2:$L$10=1),0)),-1),""))

Press Ctrl+Shift+Enter when entering the formula

In this example,

Column L is the "1" or "0" column
Column K is the sum of the child parts
Column M is where you enter this formula

If you need to sum up "Make" components that roll up to "Top Level"/Parent Parts, Then message me and I can show you what I have been working on for the past few months on how to accomplish this.

Thanks,

Hi, in the meanwhile i found the formula. = IF(A10>A9;B9;IF(A10=A9;C9:""))

Jack