I have spread sheet from a mainframe download listing several thousand
employees of my company.
I have three fields, [Employee Name] [Manager] [Job Description] that are
of interest. The Job Description applies to the Employee not the manager,
but the manager(s) is also an employee and appears in the Employee Name field.
Job Desriptions are like, Manager of ......, Dir of......., Sr Dir of.....
where the ....is "fill in the blank.

I need to come up with a way to display the reporting order of each employee:
[Name] [Manager] [Director] [Senior Director] [Vice President]
John Sam Mary Phil Joe
Helen Tom Paul Carol Jon
Sam Mary Phil Joe

Note that John reports to Sam, Sam reports to Mary and to display his
reporting order, he will be in the Name field, not Manager field, since he is
an employee as well as a manager

Suggestions on how get this done is desparately needed.

Thanks in advance
Pepper

Are you looking to just show the rows with employees only? If so, simply sort
by manager and select nonblanks.

"Pepper" wrote:

I have spread sheet from a mainframe download listing several thousand
employees of my company.
I have three fields, [Employee Name] [Manager] [Job Description] that are
of interest. The Job Description applies to the Employee not the manager,
but the manager(s) is also an employee and appears in the Employee Name field.
Job Desriptions are like, Manager of ......, Dir of......., Sr Dir of.....
where the ....is "fill in the blank.

I need to come up with a way to display the reporting order of each employee:
[Name] [Manager] [Director] [Senior Director] [Vice President]
John Sam Mary Phil Joe
Helen Tom Paul Carol Jon
Sam Mary Phil Joe

Note that John reports to Sam, Sam reports to Mary and to display his
reporting order, he will be in the Name field, not Manager field, since he is
an employee as well as a manager

Suggestions on how get this done is desparately needed.

Thanks in advance
Pepper

Thanks for the feedback Sean but if it was that simple, I would not need help.
I need to show the reporting hierarchy of each employee regardless of their
job title

[Name] [Manager] [Director] [Senior Director] [Vice President]
John Sam Mary Phil Joe
Helen Tom Paul Carol Jon
Sam Mary Phil Joe
Mary Phil Joe

Sam is both the manager of John and an Employee (in the name field)
reporting to Mary who is a Director.
Mary (the employee/director) reports to Phil.

"Sean Timmons" wrote:

Are you looking to just show the rows with employees only? If so, simply sort
by manager and select nonblanks.

"Pepper" wrote:

I have spread sheet from a mainframe download listing several thousand
employees of my company.
I have three fields, [Employee Name] [Manager] [Job Description] that are
of interest. The Job Description applies to the Employee not the manager,
but the manager(s) is also an employee and appears in the Employee Name field.
Job Desriptions are like, Manager of ......, Dir of......., Sr Dir of.....
where the ....is "fill in the blank.

I need to come up with a way to display the reporting order of each employee:
[Name] [Manager] [Director] [Senior Director] [Vice President]
John Sam Mary Phil Joe
Helen Tom Paul Carol Jon
Sam Mary Phil Joe

Note that John reports to Sam, Sam reports to Mary and to display his
reporting order, he will be in the Name field, not Manager field, since he is
an employee as well as a manager

Suggestions on how get this done is desparately needed.

Thanks in advance
Pepper

So, you are saying that you have 3 columns like this:

[Name] [Supervisor] [Job Desc]
John Sam Worker
Helen Tom Worker
Tom Paul Manager
Sam Mary Manager
Mary Phil Director
Phil Joe Sr Director
Joe Vice P
Phil Joe Sr Director
Paul Carol Director
Carol Jon Sr Director
Jon Vice P

and you want to transpose these columns into your hierarchies?

What are the different categories of Job Descrion that you can have?
Are all the names unique?

Pete

On Feb 11, 6:01*pm, Pepper wrote:
Thanks for the feedback Sean but if it was that simple, I would not need help.
I need to show the reporting hierarchy of each employee regardless of their
job title

*[Name] *[Manager] *[Director] * [Senior Director] *[Vice President]
*John * * * *Sam * * * * * *Mary * * * * * Phil * * * * * * * * * * Joe
*Helen * * * Tom * * * * * *Paul * * * * * *Carol * * * * * * * * *Jon
*Sam * * * * * * * * * * * * * Mary * * * * * Phil * * * * * * * * * * Joe
Mary * * * * * * * * * * * * * * * * * * * * * * *Phil * * * * * * * * * * Joe

Sam is both the manager of John and an Employee (in the name field)
reporting to Mary who is a Director.
Mary (the employee/director) reports to Phil.



"Sean Timmons" wrote:
Are you looking to just show the rows with employees only? If so, simply sort
by manager and select nonblanks.

"Pepper" wrote:

I have spread sheet from a mainframe download listing several thousand *
employees of my company.
I have three fields, [Employee Name] *[Manager] *[Job Description] that are
of interest. *The Job Description applies to the Employee not the manager,
but the manager(s) is also an employee and appears in the Employee Name field.
Job Desriptions are like, Manager of ......, Dir of......., Sr Dir of......
where the ....is "fill in the blank.

*I need to come up with a way to display the reporting order of each employee:
[Name] *[Manager] *[Director] * [Senior Director] *[Vice President]
John * * * *Sam * * * * * *Mary * * * * * Phil * * * * * * * * * * Joe
Helen * * * Tom * * * * * *Paul * * * * * *Carol * * * * * * * * *Jon
Sam * * * * * * * * * * * * * Mary * * * * * Phil * * * * * * * * * * Joe

Note that John reports to Sam, Sam reports to Mary and to display his
reporting order, he will be in the Name field, not Manager field, since he is
an employee as well as a manager

Suggestions on how get this done is desparately needed.

Thanks in advance
Pepper- Hide quoted text -

- Show quoted text -

I was hoping that you would answer my queries while I was out. In the
absence of that, I'll describe what I've done so far. Using the data
from my earlier post in cells A1:C11, I sorted the data by name and
added a few amendments, like so:

[Name] [Supervisor] [Job Desc]
Carol Jon Sr Director
Helen Tom Worker
Joe zzz Vice P
John Sam Worker
Jon zzz Vice P
Mary Phil Director
Paul Carol Director
Phil Joe Sr Director
Sam Mary Manager
Tom Paul Manager
zzz zzz

Notice that I have put a null record at the bottom (row 12) and
amended Joe's and Jon's record to point to this one.

Then I left a bit of a gap and put these headings on the top row:

H1: Name
I1: Manager
J1: Director
K1: Sr Director
L1: Vice P

Note that these match the entries in column C.

Then I put the names in H2:H11 (in a different order, so as to test
the solution better - see below), and then I put these formulae in the
cells stated:

I2: =IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(H2,$A:$A,0 )),$A:$A,0))=I
$1,INDEX($B:$B,MATCH(H2,$A:$A,0)),"")

J2: =IF(I2="",IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(H 2,$A:$A,0)),
$A:$A,0))=J$1,INDEX($B:$B,MATCH(H2,$A:$A,0)),""),I F(INDEX($C:$C,MATCH
(INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))=J$1,INDE X($B:$B,MATCH(I2,$A:
$A,0)),""))

K2: =IF(J2="",IF(I2="",IF(INDEX($C:$C,MATCH(INDEX($B:$ B,MATCH(H2,$A:
$A,0)),$A:$A,0))=K$1,INDEX($B:$B,MATCH(H2,$A:$A,0) ),""),IF(INDEX($C:
$C,MATCH(INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))= K$1,INDEX($B:$B,MATCH
(I2,$A:$A,0)),"")),IF(INDEX($C:$C,MATCH(INDEX($B:$ B,MATCH(J2,$A:$A,0)),
$A:$A,0))=K$1,INDEX($B:$B,MATCH(J2,$A:$A,0)),""))

L2: =IF(K2="",IF(J2="",IF(I2="",IF(INDEX($C:$C,MATCH(I NDEX($B:
$B,MATCH(H2,$A:$A,0)),$A:$A,0))=L$1,INDEX($B:$B,MA TCH(H2,$A:$A,
0)),""),IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(I2, $A:$A,0)),$A:$A,0))=L
$1,INDEX($B:$B,MATCH(I2,$A:$A,0)),"")),IF(INDEX($C :$C,MATCH(INDEX($B:
$B,MATCH(J2,$A:$A,0)),$A:$A,0))=L$1,INDEX($B:$B,MA TCH(J2,$A:$A,
0)),"")),IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(K2 ,$A:$A,0)),$A:$A,0))
=L$1,INDEX($B:$B,MATCH(K2,$A:$A,0)),""))

The formulae were then copied down to row 11, and this is what I got:

Name Manager Director Sr Director Vice P
John Sam Mary Phil Joe
Helen Tom Paul Carol Jon
Tom Paul Carol Jon
Sam Mary Phil Joe
Mary Phil Joe
Paul Carol Jon
Phil Joe
Carol Jon
Jon
Joe

Here I had arranged the names so that the "workers" were first,
followed by Managers, followed by Directors etc, as this shows the
hierarchy off more clearly (and mimics your example data better).
However, you can put the names in any order. You don't even need to
list them all, as in your example - but you will get a row of #N/A if
you leave the formulae in.

You will have to amend this to be able to cope with Job Descriptions
beginning with "Director of ...", maybe by using LEFT(...), but
hopefully you can see how it was put together.

Hope this helps.

Pete



On Feb 11, 6:42*pm, Pete_UK wrote:
So, you are saying that you have 3 columns like this:

[Name] * * *[Supervisor] * * *[Job Desc]
John * * * * *Sam * * * * * * * * Worker
Helen * * * *Tom * * * * * * * * *Worker
Tom * * * * * Paul * * * * * * * * Manager
Sam * * * * *Mary * * * * * * * * Manager
Mary * * * * *Phil * * * * * * * * * Director
Phil * * * * * Joe * * * * * * * * * *Sr Director
Joe * * * * * * * * * * * * * * * * * *Vice P
Phil * * * * * Joe * * * * * * * * * *Sr Director
Paul * * * * *Carol * * * * * * * * *Director
Carol * * * * Jon * * * * * * * * * * Sr Director
Jon * * * * * * * * * * * * * * * * * * Vice P

and you want to transpose these columns into your hierarchies?

What are the different categories of Job Descrion that you can have?
Are all the names unique?

Pete

On Feb 11, 6:01*pm, Pepper wrote:



Thanks for the feedback Sean but if it was that simple, I would not need help.
I need to show the reporting hierarchy of each employee regardless of their
job title

*[Name] *[Manager] *[Director] * [Senior Director] *[Vice President]
*John * * * *Sam * * * * * *Mary * * * * * Phil * * * * * * * * * * Joe
*Helen * * * Tom * * * * * *Paul * * * * * *Carol * * * * * * * * *Jon
*Sam * * * * * * * * * * * * * Mary * * * * * Phil * * * * * * * * * * Joe
Mary * * * * * * * * * * * * * * * * * * * * * * *Phil * * * * * * * * * * Joe

Sam is both the manager of John and an Employee (in the name field)
reporting to Mary who is a Director.
Mary (the employee/director) reports to Phil.

"Sean Timmons" wrote:
Are you looking to just show the rows with employees only? If so, simply sort
by manager and select nonblanks.

"Pepper" wrote:

I have spread sheet from a mainframe download listing several thousand *
employees of my company.
I have three fields, [Employee Name] *[Manager] *[Job Description] that are
of interest. *The Job Description applies to the Employee not the manager,
but the manager(s) is also an employee and appears in the Employee Name field.
Job Desriptions are like, Manager of ......, Dir of......., Sr Dir of.....
where the ....is "fill in the blank.

*I need to come up with a way to display the reporting order of each employee:
[Name] *[Manager] *[Director] * [Senior Director] *[Vice President]
John * * * *Sam * * * * * *Mary * * * * * Phil * * * * * * * * * * Joe
Helen * * * Tom * * * * * *Paul * * * * * *Carol * * * * * * * * *Jon
Sam * * * * * * * * * * * * * Mary * * * * * Phil * * * * * * * * * * Joe

Note that John reports to Sam, Sam reports to Mary and to display his
reporting order, he will be in the Name field, not Manager field, since he is
an employee as well as a manager

Suggestions on how get this done is desparately needed.

Thanks in advance
Pepper- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

By the way, I then added "Position" to G1 and put this formula in G2:

=INDEX(C:C,MATCH(H2,A:A,0))

which was then copied down to give the job description against the
name.

Hope this helps.

Pete

On Feb 12, 1:18*am, Pete_UK wrote:
I was hoping that you would answer my queries while I was out. In the
absence of that, I'll describe what I've done so far. Using the data
from my earlier post in cells A1:C11, I sorted the data by name and
added a few amendments, like so:

[Name] * * *[Supervisor] * * *[Job Desc]
Carol * * * * Jon * * * * * * * * * Sr Director
Helen * * * *Tom * * * * * * * * *Worker
Joe * * * * * *zzz * * * * * * * * *Vice P
John * * * * *Sam * * * * * * * * Worker
Jon * * * * * *zzz * * * * * * * * *Vice P
Mary * * * * *Phil * * * * * * * * *Director
Paul * * * * * Carol * * * * * * * *Director
Phil * * * * * * Joe * * * * * * * * *Sr Director
Sam * * * * * Mary * * * * * * * *Manager
Tom * * * * * *Paul * * * * * * * * Manager
zzz * * * * * * zzz

Notice that I have put a null record at the bottom (row 12) and
amended Joe's and Jon's record to point to this one.

Then I left a bit of a gap and put these headings on the top row:

H1: * Name
I1: * * Manager
J1: * *Director
K1: * *Sr Director
L1: * *Vice P

Note that these match the entries in column C.

Then I put the names in H2:H11 (in a different order, so as to test
the solution better - see below), and then I put these formulae in the
cells stated:

I2: * *=IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(H2,$A:$A, 0)),$A:$A,0))=I
$1,INDEX($B:$B,MATCH(H2,$A:$A,0)),"")

J2: * *=IF(I2="",IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH( H2,$A:$A,0)),
$A:$A,0))=J$1,INDEX($B:$B,MATCH(H2,$A:$A,0)),""),I F(INDEX($C:$C,MATCH
(INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))=J$1,INDE X($B:$B,MATCH(I2,$A:
$A,0)),""))

K2: * *=IF(J2="",IF(I2="",IF(INDEX($C:$C,MATCH(INDEX($B: $B,MATCH(H2,$A:
$A,0)),$A:$A,0))=K$1,INDEX($B:$B,MATCH(H2,$A:$A,0) ),""),IF(INDEX($C:
$C,MATCH(INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))= K$1,INDEX($B:$B,MATCH
(I2,$A:$A,0)),"")),IF(INDEX($C:$C,MATCH(INDEX($B:$ B,MATCH(J2,$A:$A,0)),
$A:$A,0))=K$1,INDEX($B:$B,MATCH(J2,$A:$A,0)),""))

L2: * *=IF(K2="",IF(J2="",IF(I2="",IF(INDEX($C:$C,MATCH( INDEX($B:
$B,MATCH(H2,$A:$A,0)),$A:$A,0))=L$1,INDEX($B:$B,MA TCH(H2,$A:$A,
0)),""),IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(I2, $A:$A,0)),$A:$A,0))=L
$1,INDEX($B:$B,MATCH(I2,$A:$A,0)),"")),IF(INDEX($C :$C,MATCH(INDEX($B:
$B,MATCH(J2,$A:$A,0)),$A:$A,0))=L$1,INDEX($B:$B,MA TCH(J2,$A:$A,
0)),"")),IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(K2 ,$A:$A,0)),$A:$A,0))
=L$1,INDEX($B:$B,MATCH(K2,$A:$A,0)),""))

The formulae were then copied down to row 11, and this is what I got:

Name * Manager * Director * Sr Director * Vice P
John * * Sam * * * * Mary * * * Phil * * * * * * *Joe
Helen * *Tom * * * * Paul * * * *Carol * * * * * *Jon
Tom * * * * * * * * * * *Paul * * * *Carol * * * * * Jon
Sam * * * * * * * * * * *Mary * * * Phil * * * * * * Joe
Mary * * * * * * * * * * * * * * * * * * *Phil * * * * * Joe
Paul * * * * * * * * * * * * * * * * * * * Carol * * * * Jon
Phil * * * * * * * * * * * * * * * * * * * * * * * * * * * * Joe
Carol * * * * * * * * * * * * * * * * * * * * * * * * * * * Jon
Jon
Joe

Here I had arranged the names so that the "workers" were first,
followed by Managers, followed by Directors etc, as this shows the
hierarchy off more clearly (and mimics your example data better).
However, you can put the names in any order. You don't even need to
list them all, as in your example - but you will get a row of #N/A if
you leave the formulae in.

You will have to amend this to be able to cope with Job Descriptions
beginning with "Director of ...", maybe by using LEFT(...), but
hopefully you can see how it was put together.

Hope this helps.

Pete