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)),""),IF(INDEX($C:$C,MATCH
(INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))=J$1,INDEX($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,MATCH(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,MATCH(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
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
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 said:
Are you looking to just show the rows with employees only? If so, simply sort
by manager and select nonblanks.
:
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 themanager,
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 -
