+ Reply to Thread
Results 1 to 9 of 9

how to create (and use) an hierarchy

  1. #1
    Registered User
    Join Date
    06-22-2010
    Location
    amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    20

    how to create (and use) an hierarchy

    Hello,

    To be honest, I'm not sure if I'm even using the correct words here so that might be why my google-fu is failing me.

    I have a flat file that contains a organization. For each person you have that person's group's ORU (Organizational reporting unit) and their manager's ORU. With that, you should be able to build up an organizational hierarchy.

    A the end of the day(or week the way this is going), I'd like to be able to select ORU 10 by then have all of the ORU below it be returned so that I can, say, add up all of the costs for an Organization (IE, so me all of the CIO/IT costs. So, IT Guy USA, IT Gal China and IT intern China since they both report into the CIO.)

    Any pointers as to where I should be starting?

    I'd prefer Excel or PowerPivot type stuff vs VBA.

    Thanks!,
    Alex


    ORU Mgr | ORU Person | Person
    ----------------------------------------
    1 | 1 | CEO
    1 | 10 | CIO
    1 | 11 | HR Manager
    10 | 12 | IT Guy USA
    10 | 13 | IT Gal China
    11 | 20 | HR Person
    13 | 30 | IT intern China

  2. #2
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    651

    Re: how to create (and use) an hierarchy

    sounds to me like a pivot table should work just fine... is there something in particular not working for you?

    this can also be done with array formulas, but those are fairly complicated and difficult to explain - much easier to show.

    Can you upload a sample document with a tab demonstrating what you'd like the results to look like?

  3. #3
    Registered User
    Join Date
    06-22-2010
    Location
    amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: how to create (and use) an hierarchy

    Cool! Hopefully my description was ok and it's easy
    Attached an example as requested: hierarchyExample.xlsx

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: how to create (and use) an hierarchy

    Could this be what you are after?
    Attached Files Attached Files
    Last edited by newdoverman; 08-08-2014 at 03:41 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: how to create (and use) an hierarchy

    There's a workbook with VBA and formula-based methods at https://app.box.com/s/lw3lfk71kbc121ac768o
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    06-22-2010
    Location
    amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: how to create (and use) an hierarchy

    @newdoverman : thanks for the response but it doesn't do what i'm looking for. the pivot table only shows the top level person's YouTube viewing habits, I want the viewing habit's of that person's whole organization. Does that distinction make sense?


    @shg : That work book is pretty interesting and gets me something to play with but still doesn't do what I was looking for. So, for example: at the top level the CEO should return all of the employee IDs. (then, with something else, I could add up the Youtube view counts associated with those employee IDs)

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: how to create (and use) an hierarchy

    @apolloni

    Your data has only 1 person per ORU. If you add more and then REFRESH the Pivot table the totals for each person under each ORU is shown. To refresh the Pivot table, select a cell within the Pivot table, right click and choose REFRESH.

    I added the other column that you had that starts out with 1. You will see that the all stats are included for each ORU Mgr. I didn't know that that was the field that you wanted to do the stats on. You can include or exclude the other columns as you wish. by removing them from the field list.
    Attached Files Attached Files
    Last edited by newdoverman; 08-09-2014 at 07:53 AM.

  8. #8
    Registered User
    Join Date
    06-22-2010
    Location
    amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: how to create (and use) an hierarchy

    @newdoverman Your right, there is only one person per ORU, but what I want(need ) is the sum for the whole org tree based on one ORU. So, you pick one ORU/Person and you add the value(Views) for it and every ORU that is under it. That's what I was trying to demonstrate with the example table on the right. Those numbers are the correct numbers (but I had to calculate them manually....look at their formulas).
    I appreciate your time on this. Sorry I'm not explaining as clearly as could be.
    Last edited by apolloni; 08-09-2014 at 12:55 PM. Reason: typo

  9. #9
    Registered User
    Join Date
    06-22-2010
    Location
    amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: how to create (and use) an hierarchy

    The answer can be found here: http://www.daxpatterns.com/parent-child-hierarchies/
    It uses the DAX PATH funtions and the Powerpivot hierarchy in the Data Model view.

+ 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. Create 5 column hierarchy off of one column extract
    By phil3061 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-08-2013, 10:26 AM
  2. Formula or VBA to fill down a hierarchy to create a flat file
    By bmb163 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-28-2012, 09:34 AM
  3. Hierarchy Indented List, Table, Column and Value - need to create uniqueid column
    By mcolli01 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-06-2012, 01:09 PM
  4. How to create selction in an Hierarchy?
    By dreams in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-11-2009, 06:46 PM
  5. Create Hierarchy in Excel
    By rajeshkamath27 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 12-19-2008, 11:44 AM

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