That's okay, because I'm away this weekend as well.

Just keep to this thread when you come back, rather than start a new
one, and I'll try to look out for it.

Pete

On Feb 12, 6:16*pm, Pepper wrote:
Thank you,
I will give it a try and get back to you.
Please note that I am taking part in outside meetings for the remainder of
this week, and my company is closed on Monday (a US holiday).
I respond on Tuesday. *Pleeease keep a look out on Tuesday.



"Pete_UK" wrote:
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- Hide quoted text -

- Show quoted text -