Hirarchy Count and employee display of names

[stjoe]

Hello:

I am working on a project and have an excel file that gives me chain of command (CEO leve down to Level F- employee level). It lists employee id, name, title and dept for each employee and cascades down until there is no more levels to go (stops at the employee level).

I want to be able to count the direct reports by each manager level and show the # of direct reports that manager has. If the manager has direct reports but that employee does not have direct reports I want to put a 0 to show they do not have direct reports.


Example:

Dan Smith 3
Tina Turner 0
Lake Perry 3
Tom Cruz 1
Ron Smith 2
Terry Jake 5

Any help would be greatly appreciated.

 

[tusharm]

From id, name, title, and dept, how do you know who reports to whom?

Hello:

I am working on a project and have an excel file that gives me chain of command (CEO leve down to Level F- employee level). It lists employee id, name, title and dept for each employee and cascades down until there is no more levels to go (stops at the employee level).

I want to be able to count the direct reports by each manager level and show the # of direct reports that manager has. If the manager has direct reports but that employee does not have direct reports I want to put a 0 to show they do not have direct reports.


Example:

Dan Smith 3
Tina Turner 0
Lake Perry 3
Tom Cruz 1
Ron Smith 2
Terry Jake 5

Any help would be greatly appreciated.

 

[stjoe]

I have an access database that does the hirarchy drill down but I am not able to get counts. I exported the data into excel. The key id is the employees supervsior id the look starts with each supervisor id that is tied to the employee.

 

[tusharm]

I'm sure what you wrote makes sense to you but lacking a context I am clueless as to what "the look starts with each supervisor id that is tied to the employee" means. Maybe, you should share some sample data...

I have an access database that does the hirarchy drill down but I am not able to get counts. I exported the data into excel. The key id is the employees supervsior id the look starts with each supervisor id that is tied to the employee.

 

[stjoe]

The foundation data has the employee's id, name, job title, dept and supervisor id, supervisor name.

To build out the hirarchy the above data is used and a link is created where the supervisor id on the employee record is matched to supervisor id on another file. where the id matches the supervisor id and name of that supervisor is pulled into the report (looping occures to get the supervisor roll down to the last employee in the chain). hope this helps. I already have this data but am having trouble trying to do counts.

I am not able to post a sample on in this text box.

 

[tusharm]

OK, so now the data include supervisor id and name. That's good.

Suppose your data are in A1:{X}{n} with employee id in column A and supervisor id in column C and row 1 containing column headers.

In my sample data (that I created since you would not or could not provide it) I had a total of 26 employees (rows 2 through 27).

Then, use the formula =COUNTIF($C$2:$C$27,A2) to give you the count of direct reports.

The foundation data has the employee's id, name, job title, dept and supervisor id, supervisor name.

To build out the hirarchy the above data is used and a link is created where the supervisor id on the employee record is matched to supervisor id on another file. where the id matches the supervisor id and name of that supervisor is pulled into the report (looping occures to get the supervisor roll down to the last employee in the chain). hope this helps. I already have this data but am having trouble trying to do counts.

I am not able to post a sample on in this text box.