Sum child to parent, with multiple levels
hi,
I have what I think is a rather "simple" request.
I have data set like this:
And I want summed to something like this
ID no ParrentID 1 1 2 1 9 3 1 8 4 1 5 1 3 6 1 7 1 3 8 1 9 1 1
My actual dataset consists of 100k rows and can have up to 10 levels.ID No Childs Sum No 1 2 3 4 0 1 6 0 1 8 3 4
I will be happy with UDF, VBA or anything else as long as it gives me the wanted result
Any help will be greatly appreciated
Thanks!
Last edited by HypeTaxx; 04-23-2014 at 10:39 AM. Reason: SOLVED
Not sure what you are asking. Do your first and second table correlate? I don't think so.
Is the ID in the 1st column of table 2 equal the Parent ID in Table 1?
So looking at table 1,
Parent ID 3 has 2 children?, 1,8 and 9 have 1? What if the Parent ID is blank?
Sorry, a little slow this morning.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Yes, they correlate :-)Originally Posted by ChemistB
To take it from the top :-)
ID 1 as it has no parent is the top level and is parent for 9 and 2 (9 is directly under 1 and 9 has 2 under it) therefore I want the sum of Col 2 for 1, 9 and 2 - does it make sense
To explain parent ID 3, then yes it has to childs 5 and 7, but ID 3 has 8 as its parent ID so 5 and 7 is under 3 and 3 i under 8 equals 3 childs under 8 (3 directly related and 5 and 7 related to 3) here I need the sum Col 2 from 8, 3, 5 and 7
Does it still make sense :-)
To specify data further then if ParentID is blank then we are at the top and "just" need to sum all related child record, also some ID will not have any child records
Okay, here is a solution but it only goes through 2 levels (children and grandchildren).
In E13 copied down, I put this array formula
Formula:
=IFERROR(INDEX($A$7:$A$15, SMALL(IF($C$7:$C$15="",ROW($C$7:$C$15)-ROW($C$7)+1),ROWS($A$1:A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
In F13 copied down
In G13 copied downFormula:=COUNTIF($C$7:$C$15,E13)+COUNTIF($C$7:$C$15,INDEX($A$7:$A$15,MATCH(E13,$C$7:$C$15,0)))
Formula:=SUMIF($A$7:$A$15,E13,$B$7:$B$15)+SUMIF($C$7:$C$15,E13,$B$7:$B$15)+SUMIF($C$7:$C$15, INDEX($A$7:$A$15,MATCH(E13,$C$7:$C$15,0)),$B$7:$B$15)
Thanks, it works as described, but not the quickest, my actual data is roughly 100.000 rows where 60.000 is children - if anyone knows of a better performance way of doing the same then it will be appreciated and even more if it can handle more than 2 levels :-)
Where's the data come from? I'm guessing it's not from Excel. This would be fairly trivial to do with SQL in the database from which I'm assuming it originated (unless it's access).
It is actually from SAS, so SQL would be useable :-)
Have a look here http://yazispring.wordpress.com/2013...nd-sql-server/
OK I tried doing it in SAS, but when I use the macro mentioned I only get two tables like this
LEVEL_1
LEVEL_2Parent Child Level 9 2 1 8 3 1 3 5 1 3 7 1 1 9 1
Parent Child Level 9 2 2 8 3 2 3 5 2 3 7 2 1 9 2
I'm afraid I can't help. SAS ain't my thing, I can do this in SQL server, but have never really used SAS
found another solution in SAS - not really helpfull in this forum, but thanks for your assistance anyway.
Glad you got it sortedExcel doesn't really do hierarchical data
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks