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

S

Son

Active Member
Joined
Mar 19, 2010
Messages
284
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!


[TABLE="width: 1249"]
<colgroup><col><col><col span="3"><col><col><col span="3"><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A [/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ALL[/TD]
[TD]LEVEL 1[/TD]
[TD]LEVEL 2[/TD]
[TD]LEVEL 3[/TD]
[TD][/TD]
[TD]LEVEL 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]COMPANY STRUCTURE[/TD]
[TD]PEOPLE[/TD]
[TD]1ST[/TD]
[TD]2ND[/TD]
[TD]3RD[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CEO[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]DIRECTOR 1[/TD]
[TD]JOHN[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]MARY[/TD]
[TD]DIRECTOR 1[/TD]
[TD] [/TD]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 1[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 2[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 3[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 4[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 5[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]MANAGER 1[/TD]
[TD]HELEN[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 6[/TD]
[TD]MANAGER 1[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 7[/TD]
[TD]MANAGER 1[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 8[/TD]
[TD]MANAGER 1[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 9[/TD]
[TD]MANAGER 1[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 10[/TD]
[TD]MANAGER 1[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 11[/TD]
[TD]MANAGER 1[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 12[/TD]
[TD]MANAGER 1[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8[/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 13[/TD]
[TD]MANAGER 1[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8[/TD]
[TD] [/TD]
[TD]EMPLOYEE 14[/TD]
[TD]MANAGER 1[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]MANAGER 2[/TD]
[TD]GEORGE[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 15[/TD]
[TD]MANAGER 2[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 16[/TD]
[TD]MANAGER 2[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 17[/TD]
[TD]MANAGER 2[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 18[/TD]
[TD]MANAGER 2[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 19[/TD]
[TD]MANAGER 2[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 20[/TD]
[TD]MANAGER 2[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 21[/TD]
[TD]MANAGER 2[/TD]
[TD]SUBDIRECTOR 1[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SUBDIRECTOR 2[/TD]
[TD]NICK[/TD]
[TD]DIRECTOR 1[/TD]
[TD] [/TD]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]MANAGER 3[/TD]
[TD]MICHAEL[/TD]
[TD]SUBDIRECTOR 2[/TD]
[TD]DIRECTOR 1[/TD]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 22[/TD]
[TD]MANAGER 3[/TD]
[TD]SUBDIRECTOR 2[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 23[/TD]
[TD]MANAGER 3[/TD]
[TD]SUBDIRECTOR 2[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 24[/TD]
[TD]MANAGER 3[/TD]
[TD]SUBDIRECTOR 2[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 25[/TD]
[TD]MANAGER 3[/TD]
[TD]SUBDIRECTOR 2[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 26[/TD]
[TD]MANAGER 3[/TD]
[TD]SUBDIRECTOR 2[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 27[/TD]
[TD]MANAGER 3[/TD]
[TD]SUBDIRECTOR 2[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 28[/TD]
[TD]MANAGER 3[/TD]
[TD]SUBDIRECTOR 2[/TD]
[TD]DIRECTOR 1[/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]DIRECTOR 2[/TD]
[TD]JIM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SUBDIRECTOR 3[/TD]
[TD]JACK[/TD]
[TD]DIRECTOR 2[/TD]
[TD] [/TD]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]MANAGER 4[/TD]
[TD]PETER[/TD]
[TD]SUBDIRECTOR 3[/TD]
[TD]DIRECTOR 2[/TD]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 1[/TD]
[TD]MANAGER 4[/TD]
[TD]SUBDIRECTOR 3[/TD]
[TD]DIRECTOR 2[/TD]
[/TR]
[TR]
[TD]53[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 2[/TD]
[TD]MANAGER 4[/TD]
[TD]SUBDIRECTOR 3[/TD]
[TD]DIRECTOR 2[/TD]
[/TR]
[TR]
[TD]54[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 3[/TD]
[TD]MANAGER 4[/TD]
[TD]SUBDIRECTOR 3[/TD]
[TD]DIRECTOR 2[/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 4[/TD]
[TD]MANAGER 4[/TD]
[TD]SUBDIRECTOR 3[/TD]
[TD]DIRECTOR 2[/TD]
[/TR]
[TR]
[TD]56[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 5[/TD]
[TD]MANAGER 4[/TD]
[TD]SUBDIRECTOR 3[/TD]
[TD]DIRECTOR 2[/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]58[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]DIRECTOR 3[/TD]
[TD]JIM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SUBDIRECTOR 3[/TD]
[TD]NOONE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]62[/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]MANAGER 5[/TD]
[TD]PETER[/TD]
[TD]DIRECTOR 3[/TD]
[TD] [/TD]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]63[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 1[/TD]
[TD]MANAGER 5[/TD]
[TD] [/TD]
[TD]DIRECTOR 3[/TD]
[/TR]
[TR]
[TD]64[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 2[/TD]
[TD]MANAGER 5[/TD]
[TD] [/TD]
[TD]DIRECTOR 3[/TD]
[/TR]
[TR]
[TD]65[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 3[/TD]
[TD]MANAGER 5[/TD]
[TD] [/TD]
[TD]DIRECTOR 3[/TD]
[/TR]
[TR]
[TD]66[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 4[/TD]
[TD]MANAGER 5[/TD]
[TD] [/TD]
[TD]DIRECTOR 3[/TD]
[/TR]
[TR]
[TD]67[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]EMPLOYEE[/TD]
[TD]EMPLOYEE 5[/TD]
[TD]MANAGER 5[/TD]
[TD] [/TD]
[TD]DIRECTOR 3[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
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)
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Work Order break down by Employee and Manager.
Replies
1
Views
87
rowebca
rowebca
P
  • Question Question
Rank with Multiple Criteria
Replies
2
Views
479
Cubist
Cubist
P
  • Question Question
Report with Grouping
2
Replies
19
Views
459
DanteAmor
DanteAmor
E
  • Solved
Checking if employees have completed training? Vlookup, countifs, ... ?
Replies
9
Views
235
esgca2010
E
P
  • Solved
Employee Roster Separation
Replies
9
Views
230
Sequoyah
Sequoyah

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top