Hi, I have data that looks like columns 1 and 2 of the following:
<tbody>
</tbody>
The script below works to build column 3 as an index, and I am trying to build column 4 to match the expected values in column 5, where each output is the sum of its own Value plus all direct child values. Obviously I am missing something with that for/do nested loop, I just can't get my head around it. I have done a lot of searching on flattened hierarchies and nested data but and couldn't come up with an answer I could understand.
For each level it should scan downwards, summing Values until it reaches a level that is less than or equal to itself (indicating the end of that branch of the tree).
Also here is an array formula that works to build the expected output in column 5. I just can't use this because there are too many records, computer can't handle it...
=IFERROR(SUM(OFFSET(B2,,,MATCH(TRUE,A3:$A$14<=A2,0))),SUM(B2:$B$14))
Any help is much appreciated!
| Level | Value | Tree Index | Current Output | Expected Output |
| 1 | 1 | 1 | 1 | 3 |
| 2 | 1 | 1.1 | 1 | 2 |
| 3 | 1 | 1.1.1 | 1 | 1 |
| 1 | 1 | 2 | 1 | 6 |
| 2 | 1 | 2.1 | 1 | 5 |
| 3 | 3 | 2.1.1 | 3 | 3 |
| 3 | 0 | 2.1.2 | 0 | 1 |
| 4 | 1 | 2.1.2.1 | 1 | 1 |
| 1 | 2 | 3 | 2 | 4 |
| 2 | 1 | 3.1 | 1 | 1 |
| 3 | 0 | 3.1.1 | 0 | 0 |
| 2 | 1 | 3.2 | 1 | 1 |
<tbody>
</tbody>
The script below works to build column 3 as an index, and I am trying to build column 4 to match the expected values in column 5, where each output is the sum of its own Value plus all direct child values. Obviously I am missing something with that for/do nested loop, I just can't get my head around it. I have done a lot of searching on flattened hierarchies and nested data but and couldn't come up with an answer I could understand.
For each level it should scan downwards, summing Values until it reaches a level that is less than or equal to itself (indicating the end of that branch of the tree).
Also here is an array formula that works to build the expected output in column 5. I just can't use this because there are too many records, computer can't handle it...
=IFERROR(SUM(OFFSET(B2,,,MATCH(TRUE,A3:$A$14<=A2,0))),SUM(B2:$B$14))
Any help is much appreciated!
Code:
Sub CalculateHierarchy()
Dim rLevels As Range, rLevel As Range, rVals As Range, rVal As Range
Dim level As Integer, maxLevels As Integer, val As Integer, cur As Integer, i As Integer
Dim h As String, j As Long, counts() As Integer
Set rLevels = Range("A2:A" & Range("A1").End(xlDown).Row)
maxLevels = WorksheetFunction.Max(rLevels)
Set rVals = Range("B2:B" & Range("B1").End(xlDown).Row)
ReDim counts(1 To maxLevels)
cur = 1
For Each rLevel In rLevels
level = rLevel.Value
If level > cur + 1 Then
rLevel.Activate
MsgBox "error at row " & rLevel.Row & " level increase by more than 1"
Exit Sub
End If
h = ""
counts(level) = counts(level) + 1
For i = 1 To level
h = h & "." & counts(i)
Next
h = Mid(h, 2)
For i = level + 1 To UBound(counts)
counts(i) = 0
Next
j = 0
For Each rVal In rVals
val = rLevel.Offset(, 1).Value
Do Until j = level
val = val + rLevel.Offset(, 1).Value
j = j + 1
Loop
Next
rLevel.Offset(, 2).Value = h
rLevel.Offset(, 3).Value = val
val = 0
cur = level
Next
End Sub
Last edited:
