Parent Child derivation

JackNijssen

New Member
Joined
Jun 26, 2019
Messages
2
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

Board Regular
Joined
Mar 22, 2019
Messages
86
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,
 
Last edited:

JackNijssen

New Member
Joined
Jun 26, 2019
Messages
2
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
 

Forum statistics

Threads
1,078,240
Messages
5,339,038
Members
399,275
Latest member
amrita17170909

Some videos you may like

This Week's Hot Topics

Top