Create org hierarchy in Pivot tables - i am SURE it can be done but i dont know how :(

[glm_27883]

Hello everyone

I THINK pivot tables are my answer....but I don't know how to make them work in the way I want . I am using Excel 2013 on Windows 8.1

I have a database of raw information and essentially I want to be able to produce a Org Structure in a table on a new sheet:
- far left column (eg A)would hold the most senior person,
-column B would hold their direct reports,
-column C would then hold team members (1 row per person in each column). Does that make sense? (I will have approx. 4-6 columns in the 'output org structure' but this principle remains)

In terms of the raw data, I have one large worksheet with:
-one row per person in the organisation (so >10,000 rows....)
-columns include person name, hierarchy level, unique ID, supervisor unique ID, supervisor name

There are more but I think this might be enough (if its not please say and i'll list the other columns)

Is anyone able to help? Let me know if more information is required - apologies in advance if its a poor question
Thank you!

Gavin

 

[mole999]

assuming that you don't have a numerical hierarchy already, I would guess that the boss is 1, direct reports become 2 etc, though I can't envisage how 10,000 would fit on one sheet. quite a bit of manual work though.
You did say you have hierarchy level so can that be twisted to form a down number system

In your pivot, put that number level first

 

[glm_27883]

Thanks for the reply - yes 1 = CEO, 2 = Leadership team etc etc...all the way down to about 10.
I wont be wanting the output org chart for 10,000 - I want to be able to create one for a segment eg if I know the manager at level 5 I can create the org chart under him/her...

Not totally sure I follow your comment...I do indeed have a 'hierarchy' column....not sure what you mean 'put that number level first'...sorry/...its Monday...maybe my brain is taking longer to engage

 

[mole999]

in your new pivot, use you hierarchy column to set the order, names in next column, although you could just sort the data by that column, lowest to highest and not even use a pivot

 

[sureshkc]

Hi Friends,

I also have same issue. but i didnt understand the solution. please can you explain.

Thanks in advance.
Suresh