Line Manager Formula/Macro

[ryansm05]

Hi,

I need to build a report that can look down a list of line managers and determine the employees that sit under them whether directly or indirectly. For example, the CEO may have 5 direct reports and each of those reports have a further 20 reports.

The formula would need to return the names (or unique employee reference number which may be easier) of the 105 employees that sit under the CEO plus himself. Total would be 106 employees listed by name or employee code.

Similarly, if I picked a manager who had 3 direct reports and these 3 reports were not managers, the formula/macro would finish with these 3 names plus the manager himself. So 4 in total.

I'm restricted by company security on what I can upload online but have provided an example below.

Searching for Steve Fowler and from the RESULT column you can see that ALL employees report into Steve Fowler either directly or indirectly asides from James Windpipe & Jimmy Vialli.

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 378"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Employee Number[/TD]
[TD]Name[/TD]
[TD]Line Manager[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][TABLE="width: 126"]
<colgroup><col></colgroup><tbody>[TR]
[TD]RESULT[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]<strike></strike>[TABLE="width: 378"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]1[/TD]
[TD]Ryan Smith[/TD]
[TD]Steve Fowler[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jack Brown[/TD]
[TD]Steve Fowler[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tom Judge[/TD]
[TD]Steve Fowler[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Harry Wilcox[/TD]
[TD]Steve Fowler[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]James Windpipe[/TD]
[TD]Bobby Henderson[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Michael Rooney[/TD]
[TD]Steve Fowler[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Harry Sherwood[/TD]
[TD]Steve Fowler[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Brad Hislop[/TD]
[TD]Steve Fowler[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Steve Fowler[/TD]
[TD]Jose Jepro[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Rio Vidic[/TD]
[TD]Steve Fowler[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]James Eastwood[/TD]
[TD]Steve Fowler[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Barry Gerrard[/TD]
[TD]James Eastwood[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Nemanja Terry[/TD]
[TD]James Eastwood[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Phil Smalling[/TD]
[TD]James Eastwood[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Alan Rashford[/TD]
[TD]James Eastwood[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Andy Irwin[/TD]
[TD]Phil Smalling[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Jimmy Vialli [/TD]
[TD]James Windpipe[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][TABLE="width: 103"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Ryan Smith[/TD]
[/TR]
[TR]
[TD]Jack Brown[/TD]
[/TR]
[TR]
[TD]Tom Judge[/TD]
[/TR]
[TR]
[TD]Harry Wilcox[/TD]
[/TR]
[TR]
[TD]Michael Rooney[/TD]
[/TR]
[TR]
[TD]Harry Sherwood[/TD]
[/TR]
[TR]
[TD]Brad Hislop[/TD]
[/TR]
[TR]
[TD]Steve Fowler[/TD]
[/TR]
[TR]
[TD]Rio Vidic[/TD]
[/TR]
[TR]
[TD]James Eastwood[/TD]
[/TR]
[TR]
[TD]Barry Gerrard[/TD]
[/TR]
[TR]
[TD]Nemanja Terry[/TD]
[/TR]
[TR]
[TD]Phil Smalling[/TD]
[/TR]
[TR]
[TD]Alan Rashford[/TD]
[/TR]
[TR]
[TD]Andy Irwin[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>

If someone has a formula/macro for this I'd be forever grateful. Happy to assume the range starts at A1.

Thanks in advance
Ryan

 

[ryansm05]

Just giving this a little bump!

 

[RasGhul]

Here's a formula version if suits, I had to use a helper to exclude Bobby & James. If your sheet allows it these columns can be hidden;

We could also look at making a table version so new employees maybe be added;

Book1
	A	B	C	D	E	F	G	H
1	Employee Number	Name	Line Manager	RESULT	Manager	Exclude	Help1	Help2
2	1	Ryan Smith	Steve Fowler	Ryan Smith	Steve Fowler	Bobby Henderson	Steve FowlerSteve Fowler	1
3	2	Jack Brown	Steve Fowler	Jack Brown		James Windpipe	Steve FowlerSteve Fowler	1
4	3	Tom Judge	Steve Fowler	Tom Judge			Steve FowlerSteve Fowler	1
5	4	Harry Wilcox	Steve Fowler	Harry Wilcox			Steve FowlerSteve Fowler	1
6	5	James Windpipe	Bobby Henderson	Michael Rooney
7	5	Michael Rooney	Steve Fowler	Harry Sherwood			Steve FowlerSteve Fowler	1
8	6	Harry Sherwood	Steve Fowler	Brad Hislop			Steve FowlerSteve Fowler	1
9	7	Brad Hislop	Steve Fowler	Steve Fowler			Steve FowlerSteve Fowler	1
10	8	Steve Fowler	Jose Jepro	Rio Vidic			Steve FowlerJose Jepro	1
11	9	Rio Vidic	Steve Fowler	James Eastwood			Steve FowlerSteve Fowler	1
12	10	James Eastwood	Steve Fowler	Barry Gerrard			Steve FowlerSteve Fowler	1
13	11	Barry Gerrard	James Eastwood	Nemanja Terry			Steve FowlerJames Eastwood	1
14	12	Nemanja Terry	James Eastwood	Phil Smalling			Steve FowlerJames Eastwood	1
15	13	Phil Smalling	James Eastwood	Alan Rashford			Steve FowlerJames Eastwood	1
16	14	Alan Rashford	James Eastwood	Andy Irwin			Steve FowlerJames Eastwood	1
17	15	Andy Irwin	Phil Smalling				Steve FowlerPhil Smalling	1
18	16	Jimmy Vialli	James Windpipe
Sheet1

Cell Formulas
Range	Formula
G2	=IF(OR(C2=$F$2,C2=$F$3),"",$E$2&C2)
H2	=IF(ISNUMBER(SEARCH($E$2,G2)),1,"")
D2	{=IF(ROWS($D$2:D2)>COUNT(H:H),"",INDEX($B$2:$B$18,SMALL(IF($G$2:$G$18=$E$2&$C$2:$C$18,ROW($A$2:$A$18)-ROW($A$1)),ROWS($D$2:D2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ryansm05]

Thanks for this Ras - I’m not yet at my machine so can only see it on my phone.

I should have mentioned that there may be up to 20,000 employees so a helper sheet would be quite difficult to create. Apologies for not including that in my original post.

Do you think that makes it near impossible?

 

[RasGhul]

hmm they are helper columns on the same sheet as your result, although if you have 20k rows and exceptions VBA maybe a better way to go.

 

[ryansm05]

Okay no problem - although this formula is still very helpful and I will almost certainly use it for something else. So thank you!


Calling VBA experts ……. any ideas how you can potentially help?

 

[RasGhul]

Is your goal the same as saying list all employees, except for employees that have Bobby Henderson or James Windpipe as Line managers?