+ Reply to Thread
Results 1 to 12 of 12

Sum child to parent, with multiple levels

  1. #1
    Registered User
    Join Date
    02-20-2012
    Location
    wledpor should
    MS-Off Ver
    Excel 2007
    Posts
    6

    Question Sum child to parent, with multiple levels

    hi,

    I have what I think is a rather "simple" request.

    I have data set like this:

    Please Login or Register  to view this content.
    And I want summed to something like this
    Please Login or Register  to view this content.
    My actual dataset consists of 100k rows and can have up to 10 levels.


    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

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Sum child to parent, with multiple levels

    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

  3. #3
    Registered User
    Join Date
    02-20-2012
    Location
    wledpor should
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Sum child to parent, with multiple levels

    Quote Originally Posted by ChemistB View Post
    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.
    Yes, they correlate :-)

    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

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Sum child to parent, with multiple levels

    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: copy to clipboard
    Please Login or Register  to view this content.

    ...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
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In G13 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-20-2012
    Location
    wledpor should
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Sum child to parent, with multiple levels

    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 :-)

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Sum child to parent, with multiple 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).

  7. #7
    Registered User
    Join Date
    02-20-2012
    Location
    wledpor should
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Sum child to parent, with multiple levels

    Quote Originally Posted by Kyle123 View Post
    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 :-)

  8. #8
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Sum child to parent, with multiple levels


  9. #9
    Registered User
    Join Date
    02-20-2012
    Location
    wledpor should
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Sum child to parent, with multiple levels

    Quote Originally Posted by Kyle123 View Post

    OK I tried doing it in SAS, but when I use the macro mentioned I only get two tables like this

    LEVEL_1

    Please Login or Register  to view this content.
    LEVEL_2

    Please Login or Register  to view this content.

  10. #10
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Sum child to parent, with multiple levels

    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

  11. #11
    Registered User
    Join Date
    02-20-2012
    Location
    wledpor should
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Sum child to parent, with multiple levels

    found another solution in SAS - not really helpfull in this forum, but thanks for your assistance anyway.

  12. #12
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Sum child to parent, with multiple levels

    Glad you got it sorted Excel doesn't really do hierarchical data

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Parent child relationships(working out parent item) for each item
    By grphillips in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-21-2013, 05:58 AM
  2. Vba code for Parent child
    By ken4ward in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-06-2012, 06:04 AM
  3. Parent Child Macro
    By ckattookaran in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-06-2012, 11:50 AM
  4. Sorting Parent Child
    By kcmtnbiker in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-30-2006, 09:00 PM
  5. Nested Withs of Multiple Objects (Parent-Child)
    By Trip in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-03-2005, 05:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1