Find people responsible for employees withing a hierachic structure in a company.

[Son]

Hello, I need to automatically find the managers who will be responsible for each employee in a company. There are many hierarchy levels and each manager is responsible for his team of subordinates.

I have all the columns A - K and I need to fill in columns L-M-N with the names (or job title) of the people corresponding to each person, as in the following example.

I have no idea how to approach this. Data in columns A-K is from a pivot table based on the employees' database.

I have classified each person according to a hierarchy level.
For example CEO = level 1, Director = Level 2, Subdirector = level 3, managers = level 5. People in level 4, or levels 6, 7 and 8 are not responsible for any subordinates.

So, in column A I have all hierarchy levels and in columns B-K I have the hierachy level for each person.

Each SubDirector is only responsible for the people that work for him, the same goes for each manager etc.

I don't know if this makes any sense to you, but if it does, I would be really greatful for any suggestion you might have.

I'm looking forward to any thoughts or advice.

Thanks in advance!

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

ALL

LEVEL 1

LEVEL 2

LEVEL 3

LEVEL 5

COMPANY STRUCTURE

PEOPLE

1ST

2ND

3RD

2

3

1

1

CEO

4

5

2

2

DIRECTOR 1

JOHN

CEO

6

7

3

3

SUBDIRECTOR 1

MARY

DIRECTOR 1

CEO

8

9

4

4

EMPLOYEE

EMPLOYEE 1

SUBDIRECTOR 1

DIRECTOR 1

CEO

10

EMPLOYEE

EMPLOYEE 2

SUBDIRECTOR 1

DIRECTOR 1

CEO

11

EMPLOYEE

EMPLOYEE 3

SUBDIRECTOR 1

DIRECTOR 1

CEO

12

EMPLOYEE

EMPLOYEE 4

SUBDIRECTOR 1

DIRECTOR 1

CEO

13

EMPLOYEE

EMPLOYEE 5

SUBDIRECTOR 1

DIRECTOR 1

CEO

14

15

5

5

MANAGER 1

HELEN

SUBDIRECTOR 1

DIRECTOR 1

CEO

16

6

6

EMPLOYEE

EMPLOYEE 6

MANAGER 1

SUBDIRECTOR 1

DIRECTOR 1

17

6

6

EMPLOYEE

EMPLOYEE 7

MANAGER 1

SUBDIRECTOR 1

DIRECTOR 1

18

6

6

EMPLOYEE

EMPLOYEE 8

MANAGER 1

SUBDIRECTOR 1

DIRECTOR 1

19

6

6

EMPLOYEE

EMPLOYEE 9

MANAGER 1

SUBDIRECTOR 1

DIRECTOR 1

20

6

6

EMPLOYEE

EMPLOYEE 10

MANAGER 1

SUBDIRECTOR 1

DIRECTOR 1

21

7

7

EMPLOYEE

EMPLOYEE 11

MANAGER 1

SUBDIRECTOR 1

DIRECTOR 1

22

7

7

EMPLOYEE

EMPLOYEE 12

MANAGER 1

SUBDIRECTOR 1

DIRECTOR 1

23

8

8

EMPLOYEE

EMPLOYEE 13

MANAGER 1

SUBDIRECTOR 1

DIRECTOR 1

24

8

8

EMPLOYEE 14

MANAGER 1

SUBDIRECTOR 1

DIRECTOR 1

25

26

5

5

MANAGER 2

GEORGE

SUBDIRECTOR 1

DIRECTOR 1

CEO

27

6

6

EMPLOYEE

EMPLOYEE 15

MANAGER 2

SUBDIRECTOR 1

DIRECTOR 1

28

6

6

EMPLOYEE

EMPLOYEE 16

MANAGER 2

SUBDIRECTOR 1

DIRECTOR 1

29

6

6

EMPLOYEE

EMPLOYEE 17

MANAGER 2

SUBDIRECTOR 1

DIRECTOR 1

30

6

6

EMPLOYEE

EMPLOYEE 18

MANAGER 2

SUBDIRECTOR 1

DIRECTOR 1

31

7

7

EMPLOYEE

EMPLOYEE 19

MANAGER 2

SUBDIRECTOR 1

DIRECTOR 1

32

7

7

EMPLOYEE

EMPLOYEE 20

MANAGER 2

SUBDIRECTOR 1

DIRECTOR 1

33

7

7

EMPLOYEE

EMPLOYEE 21

MANAGER 2

SUBDIRECTOR 1

DIRECTOR 1

34

35

36

3

3

SUBDIRECTOR 2

NICK

DIRECTOR 1

CEO

37

38

5

5

MANAGER 3

MICHAEL

SUBDIRECTOR 2

DIRECTOR 1

CEO

39

6

6

EMPLOYEE

EMPLOYEE 22

MANAGER 3

SUBDIRECTOR 2

DIRECTOR 1

40

6

6

EMPLOYEE

EMPLOYEE 23

MANAGER 3

SUBDIRECTOR 2

DIRECTOR 1

41

6

6

EMPLOYEE

EMPLOYEE 24

MANAGER 3

SUBDIRECTOR 2

DIRECTOR 1

42

6

6

EMPLOYEE

EMPLOYEE 25

MANAGER 3

SUBDIRECTOR 2

DIRECTOR 1

43

7

7

EMPLOYEE

EMPLOYEE 26

MANAGER 3

SUBDIRECTOR 2

DIRECTOR 1

44

7

7

EMPLOYEE

EMPLOYEE 27

MANAGER 3

SUBDIRECTOR 2

DIRECTOR 1

45

7

7

EMPLOYEE

EMPLOYEE 28

MANAGER 3

SUBDIRECTOR 2

DIRECTOR 1

46

47

2

2

DIRECTOR 2

JIM

CEO

48

49

3

3

SUBDIRECTOR 3

JACK

DIRECTOR 2

CEO

50

51

5

5

MANAGER 4

PETER

SUBDIRECTOR 3

DIRECTOR 2

CEO

52

4

4

EMPLOYEE

EMPLOYEE 1

MANAGER 4

SUBDIRECTOR 3

DIRECTOR 2

53

EMPLOYEE

EMPLOYEE 2

MANAGER 4

SUBDIRECTOR 3

DIRECTOR 2

54

EMPLOYEE

EMPLOYEE 3

MANAGER 4

SUBDIRECTOR 3

DIRECTOR 2

55

EMPLOYEE

EMPLOYEE 4

MANAGER 4

SUBDIRECTOR 3

DIRECTOR 2

56

EMPLOYEE

EMPLOYEE 5

MANAGER 4

SUBDIRECTOR 3

DIRECTOR 2

57

58

2

2

DIRECTOR 3

JIM

CEO

59

60

3

3

SUBDIRECTOR 3

NOONE

61

62

5

5

MANAGER 5

PETER

DIRECTOR 3

CEO

63

6

6

EMPLOYEE

EMPLOYEE 1

MANAGER 5

DIRECTOR 3

64

6

6

EMPLOYEE

EMPLOYEE 2

MANAGER 5

DIRECTOR 3

65

6

6

EMPLOYEE

EMPLOYEE 3

MANAGER 5

DIRECTOR 3

66

6

6

EMPLOYEE

EMPLOYEE 4

MANAGER 5

DIRECTOR 3

67

6

6

EMPLOYEE

EMPLOYEE 5

MANAGER 5

DIRECTOR 3

<colgroup><col><col><col span="3"><col><col><col span="3"><col><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[Son]

Any thoughts or ideas? I'm really stuck on this...

 

[Son]

Please, any help?

 

[MickG]

Why are there 2 "Subdirector 3" and the 2nd one is not responsible to anyone ????

 

[Son]

Mick you're right. The 2nd Subdirector 3 should be named Subdirector 4.

The "Noone" next to the title Subdirector 4 means that the position of subdirector 4 is not filled.

So, in this last group, the employees refer to Manager 5 and Director 3 (since there is no SubDir 4).

I understand it's a bit complicated. Each employee should be monitored (and appraised) by 3 managers (columns M-O in the example, where the three managers are shown as: 1st, 2nd, 3rd)

 

[MickG]

See example file sheet 2 for possible results:-
https://app.box.com/s/7yod1ez4e4cl48d9qc3y08rjmzzzn6b6

 

[Son]

Mick, thank you so much for your help!

However, I've been trying to make it work to my prototype, but I can't. I can run the code in your example, and it works perfectly. But I cannot do it in mine.

The error I get is "subscript out of range" when in gets to this if:
For Ac = 0 To UBound(Nams)
If Split(Dn.Value, " ")(0) = Nams(Ac) Then St = Ac + 1: Exit For
Next Ac



Also, I am not able to recognise the code you wrote so as to make any conversions if necessary. But it doesn't seem to me that any conversion should take place. Maybe I'm wrong.

I'm using excel 2003, but this should not be a problem since it works in your file, saved as excel 2003.

So, any advice on how to proceed? I mean are there any restrictions on the names in column A? Or any other restrictions?

Your code is brilliant by the way, it just doesn't work in my file.

I'm looking forward to your reply.

 

[MickG]

Try the code in Sheet 1 of this modified file.
https://app.box.com/s/cz1m95ack03anbzu2ricwsifr6c9nb81