HELP: Creating an employee hierarchy from a dataset

[AdamEClark]

I am trying to create an employee hierarchy from a dataset that is provided in the following format (example):

Employee Name	Supervisor Employee Number	Supervisor Name	Level 2 Supervisor Name	Level 3 Supervisor Name	Level 4 Supervisor Name	Level 5 Supervisor Name	Level 6 Supervisor Name	Supervisor Category
Ram	1	Keith	Hugh	Mike	Keith
Stephen	2	Ram	Hugh	Mike	Keith	Ram
Annika	3	Ram	Hugh	Mike	Keith	Ram
John	4	Kerri	Hugh	Mike	Keith	Kerri
Roger	5	John	Hugh	Mike	Keith	Kerri	John
Jonathan	6	John	Hugh	Mike	Keith	Kerri	John
Stephanie	7	John	Hugh	Mike	Keith	Kerri	John
Miguel	8	Ram	Hugh	Mike	Keith	Ram
William	9	Ram	Hugh	Mike	Keith	Ram
Debby	10	Kirsi	Hugh	Mike	Keith	Kirsi
Kerri	11	Keith	Hugh	Mike	Keith
Brian	12	Kerri	Hugh	Mike	Keith	Kerri
Hugh	13
Mike	14		Hugh

My goal is to put each employee into a category as follows:

1.) Employee has multiple supervisees.

2.) Employee has one supervisee.

3.) Employee has no supervisees.

I would like to populate the far right column with a 1, 2, or 3 depending on how many supervisees the employee has.

Does anyone have any ideas for how I can do this?

[Claje · Accepted solution]

Hi,

There are a few ways to interpret this, so I have outlined a couple options

If you only care about Direct Reports:

Use a Summarize tool, Group by Supervisor Name, Count Distinct Employee Name.  Then you can Join the data back together on Employee Name = Supervisor Name, and create the categories using a Formula tool.

Your formula might look something like:

IF [CountDistinct_Employee Name] >1 THEN 1
ELSEIF [CountDistinct_Employee Name] = 1 THEN 2
ELSE 3
ENDIF

If you are looking for the total number of employees under a given individual (including indirect reports), then you will want to use a Transpose tool to start things off, to put all of the "Level # Supervisor Name" columns into one set of values.  Then you can summarize on this and use a similar method to the Direct Report method.

Hope this helps!