Parent-Child hierarchies in excel vba code

[Ganesh Babi]

Parent Name	Child Name	Amt		Output	Sum of Amt	Please help me to sort out the query
A	x	12		A	24​
B	y	24		x	17​
C	z	3		z	7​
A	x	5		B	51​
A	z	2		x	23​
B	y	4		y	28​
A	z	5		C	16​
B	x	23		x	7​
C	y	6		y	6​
C	x	7		z	3​

 

[Ganesh Babi]

Please assist me in sorting the below excel query and the expected output is showing in the below table.

Parent Name	Child Name	Amt		Output	Sum of Amt
A	x	12		A	24
B	y	24		x	17
C	z	3		z	7
A	x	5		B	51
A	z	2		x	23
B	y	4		y	28
A	z	5		C	16
B	x	23		x	7
C	y	6		y	6
C	x	7		z	3

 

[p45cal]

Do you need to use vba? This is standard pivot table stuff:

 

[Ganesh Babi]

Agreed, we can use PivotTables for this. We are planning to automate the process, which is why we need VBA code. However, we can't use too many PivotTables in the Excel sheet.

 

[p45cal]

x = [LET(a,DROP(PIVOTBY(Table1[[Parent Name]:[Child Name]],,Table1[Amt],SUM,0,-2),1),HSTACK(IF(CHOOSECOLS(a,2)="",CHOOSECOLS(a,1),CHOOSECOLS(a,2)),CHOOSECOLS(a,3)))]
Range("T2").Resize(UBound(x), UBound(x, 2)).Value = x

Table1 is the name of my source data table; adjust the code to match your table's name.

or

With Range("I2")
  .Formula2R1C1 = "=LET(a,DROP(PIVOTBY(Table1a[[Parent Name]:[Child Name]],,Table1a[Amt],SUM,0,-2),1),HSTACK(IF(CHOOSECOLS(a,2)="""",CHOOSECOLS(a,1),CHOOSECOLS(a,2)),CHOOSECOLS(a,3)))"
  .SpillingToRange.Value = .SpillingToRange.Value
End With