finding multiple parent-child relationships?
 

I have a list of a bunch of people (employees) (column A) and in another
column (B), their managers. Each employee has a single record, but a manager
name can show up multiple times. Not all employees are managers.

I need to identify which managers are "front line" managers, e.g. who do not
have any other managers reporting to them. I'd like to avoid going to VBA if
this is possible to do in a formula.

Basically, my logic needs to be: For every manager, check their direct
reports to see if they also show up in the manager column. If none do, then
flag this individual as a front line manager

The problem I'm having is figuring out how to get a formula to process
/every/ direct report. I'll need to use sumproduct or an array formula, but
I'm still getting stuck on the logic. Let's say I copy column A to column C,
just so I can do vlookups on either group.

=sumproduct ((A1:A1000=ThisManager)*(vlookup(B1:B1000,B1:C1000 ,2,False)....
but even here, I don't think the vlookup will necessarily be on the same row
as the first parts of my sumproduct?

Any ideas to get me started?

Thanks!
Keith

finding multiple parent-child relationships?
 

My best shot so far is the following, which doesn't work because (I think)
the last B:B is seen as a single cell (whichever row it is on) rather than
the full range for each comparison. Any ideas?

=SUMPRODUCT((B:B=A2)*(MATCH(A:A,B:B,FALSE)))

Thanks!

"Keith R" wrote in message
...
I have a list of a bunch of people (employees) (column A) and in another
column (B), their managers. Each employee has a single record, but a
manager name can show up multiple times. Not all employees are managers.

I need to identify which managers are "front line" managers, e.g. who do
not have any other managers reporting to them. I'd like to avoid going to
VBA if this is possible to do in a formula.

Basically, my logic needs to be: For every manager, check their direct
reports to see if they also show up in the manager column. If none do,
then flag this individual as a front line manager

The problem I'm having is figuring out how to get a formula to process
/every/ direct report. I'll need to use sumproduct or an array formula,
but I'm still getting stuck on the logic. Let's say I copy column A to
column C, just so I can do vlookups on either group.

=sumproduct
((A1:A1000=ThisManager)*(vlookup(B1:B1000,B1:C1000 ,2,False).... but even
here, I don't think the vlookup will necessarily be on the same row as the
first parts of my sumproduct?

Any ideas to get me started?

Thanks!
Keith

finding multiple parent-child relationships?
 

OK, you want to avoid VBA. Are you willing to use the MS Query tool?

To do this, you need to have a label for your employee column (maybe
something clever like "Employees") and a label for the manager column, say
"Managers" I'm going to use those labels in the example, and I named my
range MgtList

Name the entire range. Just to be safe, save your file.

Go to an empty sheet - same workbook or new - your choice.

Data-Import external data-new database query. You'll see a window for
choosing your data source. Select Excel Files and click OK. Now use the
File dialog to navigate to and double-click the saved file. You will then
see a window that will have the data's rangename listed. If you have other
names in the file, ignore them. Just select the data rangename and click on
the arrow to move all the columns to the right panel, then click on Next.
In the next 2 windows, click on Next again. In the final panel, choose to
View or Edit Query in MS Query and then click the Finish button

NOTE THAT THIS WILL ONLY FIND THOSE MANAGERS WHO HAVE only FRONT LINE
EMPLOYEES. IF A MANAGER HAS FRONT LINE EMPLOYEES AND A MANGER REPORTING TO
HIM/HER, THEY WON'T SHOW UP HERE.

In the window that comes up, select SQL from the View menu. You'll see a
window with a select statement. Delete what is there and paste this in
instead

SELECT distinct m1.Managers
FROM MgtList m1
inner join
(
select t1.employees , t1.managers
from MgtList t1
left join MgtList t2
on t1.employees = t2.managers
where t2.Managers is null
) m2
on m1.managers = m2.managers

Click on the OK button - you will see a warning that it can't be depicted
graphically. Ignore it and run the query.

When the query is done running, use the file menu and choose Return Data to
Excel


"Keith R" wrote:

I have a list of a bunch of people (employees) (column A) and in another
column (B), their managers. Each employee has a single record, but a manager
name can show up multiple times. Not all employees are managers.

I need to identify which managers are "front line" managers, e.g. who do not
have any other managers reporting to them. I'd like to avoid going to VBA if
this is possible to do in a formula.

Basically, my logic needs to be: For every manager, check their direct
reports to see if they also show up in the manager column. If none do, then
flag this individual as a front line manager

The problem I'm having is figuring out how to get a formula to process
/every/ direct report. I'll need to use sumproduct or an array formula, but
I'm still getting stuck on the logic. Let's say I copy column A to column C,
just so I can do vlookups on either group.

=sumproduct ((A1:A1000=ThisManager)*(vlookup(B1:B1000,B1:C1000 ,2,False)....
but even here, I don't think the vlookup will necessarily be on the same row
as the first parts of my sumproduct?

Any ideas to get me started?

Thanks!
Keith

finding multiple parent-child relationships?
 

Thanks Duke- I'll give that a try!
Best,
Keith

"Duke Carey" wrote in message
...
OK, you want to avoid VBA. Are you willing to use the MS Query tool?

To do this, you need to have a label for your employee column (maybe
something clever like "Employees") and a label for the manager column, say
"Managers" I'm going to use those labels in the example, and I named my
range MgtList

Name the entire range. Just to be safe, save your file.

Go to an empty sheet - same workbook or new - your choice.

Data-Import external data-new database query. You'll see a window for
choosing your data source. Select Excel Files and click OK. Now use the
File dialog to navigate to and double-click the saved file. You will then
see a window that will have the data's rangename listed. If you have
other
names in the file, ignore them. Just select the data rangename and click
on
the arrow to move all the columns to the right panel, then click on
Next.
In the next 2 windows, click on Next again. In the final panel, choose to
View or Edit Query in MS Query and then click the Finish button

NOTE THAT THIS WILL ONLY FIND THOSE MANAGERS WHO HAVE only FRONT LINE
EMPLOYEES. IF A MANAGER HAS FRONT LINE EMPLOYEES AND A MANGER REPORTING
TO
HIM/HER, THEY WON'T SHOW UP HERE.

In the window that comes up, select SQL from the View menu. You'll see a
window with a select statement. Delete what is there and paste this in
instead

SELECT distinct m1.Managers
FROM MgtList m1
inner join
(
select t1.employees , t1.managers
from MgtList t1
left join MgtList t2
on t1.employees = t2.managers
where t2.Managers is null
) m2
on m1.managers = m2.managers

Click on the OK button - you will see a warning that it can't be depicted
graphically. Ignore it and run the query.

When the query is done running, use the file menu and choose Return Data
to
Excel


"Keith R" wrote:

I have a list of a bunch of people (employees) (column A) and in another
column (B), their managers. Each employee has a single record, but a
manager
name can show up multiple times. Not all employees are managers.

I need to identify which managers are "front line" managers, e.g. who do
not
have any other managers reporting to them. I'd like to avoid going to VBA
if
this is possible to do in a formula.

Basically, my logic needs to be: For every manager, check their direct
reports to see if they also show up in the manager column. If none do,
then
flag this individual as a front line manager

The problem I'm having is figuring out how to get a formula to process
/every/ direct report. I'll need to use sumproduct or an array formula,
but
I'm still getting stuck on the logic. Let's say I copy column A to column
C,
just so I can do vlookups on either group.

=sumproduct
((A1:A1000=ThisManager)*(vlookup(B1:B1000,B1:C1000 ,2,False)....
but even here, I don't think the vlookup will necessarily be on the same
row
as the first parts of my sumproduct?

Any ideas to get me started?

Thanks!
Keith

finding multiple parent-child relationships?
 

Not quite sure whether you want to count or flag 'front line' managers.
So, assuming that...

1) you want to count

2) A2:A10 contains the employee name

3) B2:B10 contains the corresponding manager

4) D2 contains the manager of interest

Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(IF(ISNUMBER(MATCH(IF($B$2:$B$10=D2,$A$2:$A$10 ),$B$2:$B$10,0)),1))

Hope this helps!

In article ,
"Keith R" wrote:

I have a list of a bunch of people (employees) (column A) and in another
column (B), their managers. Each employee has a single record, but a manager
name can show up multiple times. Not all employees are managers.

I need to identify which managers are "front line" managers, e.g. who do not
have any other managers reporting to them. I'd like to avoid going to VBA if
this is possible to do in a formula.

Basically, my logic needs to be: For every manager, check their direct
reports to see if they also show up in the manager column. If none do, then
flag this individual as a front line manager

The problem I'm having is figuring out how to get a formula to process
/every/ direct report. I'll need to use sumproduct or an array formula, but
I'm still getting stuck on the logic. Let's say I copy column A to column C,
just so I can do vlookups on either group.

=sumproduct ((A1:A1000=ThisManager)*(vlookup(B1:B1000,B1:C1000 ,2,False)....
but even here, I don't think the vlookup will necessarily be on the same row
as the first parts of my sumproduct?

Any ideas to get me started?

Thanks!
Keith