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