Trying to create a pivot table with Org Structure hierarchy

[qaliq]

Hi everyone,

Been away from the forums for a bit as I had a bit of a career change but now i'm back working with excel and realised I've forgotten a lot!

I have a list of employee data with their employee number, managers employee number and weekly working hours. There are around 8 levels of hierarchy an about 1000 lines in my data table.

I am trying to summarise this in a pivot table which shows the average weekly working hours but with the ability to expand and contract based on manager. So at the top level you would just have the boss with an average weekly working hours and count of people in the organisation, then when you open that up you have the 10 people who work for them with their associated counts and average working hours, then you open one of these people up and you get the 6 people working for them with the count of people in their teams and the average working hours. and so on and so on.

I feel this shouldn't be too difficult but i am either getting stuck with too many blanks or a really flat structure.

As this is employee info I obviously can't upload it here however I have created an example with a small number of employees.

I'm sure this should be relatively straightforward...is it possible?

Thanks,

Alice

 

[NARAYANK991]

Hi ,

Similar questions have been asked earlier :

https://chandoo.org/forum/threads/reporting-hierarchy-from-the-sample-data-attached.15844/

https://chandoo.org/forum/threads/group-rows-and-columns-via-vba.14572/

Both of these have solutions using VBA ; see if they may be useful.

Narayan