Management hierarchy Setup

[erlisocial]

Hi all,

I am looking to group management hierarchy into 6 categories based on who is reporting to who! Working backwards from the CEO identification I would like to populate the employees in the correct groups. The data is on column A:B (Real spreadsheet is on an excel table headers Employee, Manager)

CEO : The manager value will not exist in the employee column
Directors: Employees reporting directly to CEO & managing employees. If not managing employees move to next tier down (Upper Manager)
Upper Manager: Employees reporting directly to a Director & also managing employees. If not managing employees move to next tier down (Manager)
Manager: Employee reporting to Upper Manager and manage employees. If not managing employees move to next tier down (Team Leader)
Team Leader: Employees reporting to Manager and managing employees. If not managing employees move two tiers down (Employee)
Supervisor: Employees reporting to Team Leader and managing employees. If not managing employees move one tier down (Employee)

Desirable results have been added from cell D1 onwards. Many thanks in advance

Employee	Manager	Comments	CEO	Director	Upper Manager	Manager	Team Leader	Supervisor	Employee
Employee 1	Employee 10		Employee 10	Employee 1	Employee 3	Employee 11	Employee 12	Employee 8	Employee 7
Employee 2	Employee 6			Employee 6		Employee 2	Employee 4
Employee 3	Employee 1					Employee 5	Employee 9
Employee 4	Employee 3					Employee 11
Employee 5	Employee 1
Employee 6	Employee 10
Employee 7	Employee 8
Employee 8	Employee 12
Employee 9	Employee 3
Employee 10	Employee X
Employee 11	Employee 6
Employee 12	Employee 3