• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

roll up sum of time spent from children rows to parents

R

ronencozen

New Member
Hello all,


Having the following excel table, I would like to roll up the sum of TimeSpent from level 3 rows to their parents in level 2 and 1, the column CalcTimeSpent show the expected results.

VBA code is preferable.


Thanks in advanced,

Ronen

[pre]
Code: 
RowID	Level	TimeSpent	CalcTimeSpent
1	1	-	        3
2	2	-	        2
3	3	1	        -
4	3	1	        -
5	2	-	        1
6	3	1	        -
7	1	-	        -
[/pre]
 
Hi ronencozen,


Welcome to the forums, will you explain by taking up an example from the data.


Regards,
 
Sure!

The table represents a 3 level hierarchy, e.g. row #3 & #4 are immediate children of #2 , hence the calculated time spent for #2 is the sum of time spent on #3 and #4, which is 2 (1+1).

#2 is a child of #1 and also #5 is a child of #1, hence the calculated time spent for row 1 is 3 (2+1).


HTH


Ronen
 
Hi Ronen ,


There seems to be some inconsistency in your explanation.


You say #3 and #4 are immediate children of 2 ; I assume this is because they follow immediately after 2 ; but your next statement says #2 is a child of #1 , and also #5 is a child of 1. If we take the latter statement as true , then proximity is not important , since #5 is not immediately after #1.


Secondly , if #2 and #5 are children of #1 , then #6 is also a child of #2 , since it is also at level 3.


Can you explain exactly what is the criterion for deciding which rowID is a child of which other rowID ?


Narayan
 
Hi Narayan,


Proximity is important, more accurately the ordering of the records is important and indicate which rowID is a child and who is the parent.


Under thevassumption that ordering is important your second statement is not valid, since #6 should relate to his immediate parent in order which is #5.


Ronen
 
Hi Ronen ,


I am not making any statement ; I am only trying to understand yours !


You have made two statements , which are :


1. row #3 & #4 are immediate children of #2


Your use of the word "immediate" made me assume that proximity is important ; so far so good.


Now you make another statement :


2. #2 is a child of #1 and also #5 is a child of #1


Here #5 is not immediately after #1 , so how can #5 be the child of #1 ? Can you clarify ?


My statement regarding #6 was based on your second statement , since if #5 can be a child of #1 , then by the same token , #6 can be a child of #2.


Narayan
 
Try assuming the posted data is in column A to C and you want results in column D:

[pre]
Code: 
Public Sub GetParentTotals()
Dim i As Long, lngSum2 As Long, lngSum3 As Long
lngSum2 = 0: lngSum3 = 0
For i = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
If Range("B" & i).Value = 1 Then        'Value equals 1
If lngSum2 > 0 Then
Range("D" & i).Value = lngSum2
Else
Range("D" & i).Value = "-"
End If
lngSum2 = 0: lngSum3 = 0
ElseIf Range("B" & i).Value = 2 Then    'Value equals 2
Range("D" & i).Value = lngSum3
lngSum2 = lngSum2 + lngSum3
lngSum3 = 0
Else                                    'Value equals 3
lngSum3 = lngSum3 + Range("C" & i).Value
Range("D" & i).Value = "-"
End If
Next i
End Sub
[/pre]
 
Since my code worked I think I can explain ron's requirement.


It is similar to treeview control or windows explorer. Since the data is in one column it made for little unclear picture / concept.

[pre]
Code: 
1--
|-2
|  |-3
|  |-3
|
|-2
|-3
1
[/pre]

* Topmost element is 1 and bottommost element is 3

* So the parent is one number less and resides somewhere in the upper row. Until then you can keep looping upwards.
 
You must log in or register to reply here.
Back
Top