Line Manager Formula/Macro

ryansm05

Board Regular
Joined
Sep 14, 2016
Messages
127
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.

Employee NumberNameLine Manager

<colgroup><col span="3"></colgroup><tbody>
</tbody>
<strike></strike>
RESULT

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>
<strike></strike>
1Ryan SmithSteve Fowler
2Jack BrownSteve Fowler
3Tom JudgeSteve Fowler
4Harry WilcoxSteve Fowler
5James WindpipeBobby Henderson
5Michael RooneySteve Fowler
6Harry SherwoodSteve Fowler
7Brad HislopSteve Fowler
8Steve FowlerJose Jepro
9Rio VidicSteve Fowler
10James EastwoodSteve Fowler
11Barry GerrardJames Eastwood
12Nemanja TerryJames Eastwood
13Phil SmallingJames Eastwood
14Alan RashfordJames Eastwood
15Andy IrwinPhil Smalling
16Jimmy Vialli James Windpipe

<colgroup><col span="3"></colgroup><tbody>
</tbody>
<strike></strike>
Ryan Smith
Jack Brown
Tom Judge
Harry Wilcox
Michael Rooney
Harry Sherwood
Brad Hislop
Steve Fowler
Rio Vidic
James Eastwood
Barry Gerrard
Nemanja Terry
Phil Smalling
Alan Rashford
Andy Irwin

<colgroup><col></colgroup><tbody>
</tbody>
<strike></strike>

<tbody>
</tbody>
<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
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
607
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;


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: right;;">Employee Number</td><td style="font-weight: bold;text-align: right;;">Name</td><td style="font-weight: bold;text-align: right;;">Line Manager</td><td style="font-weight: bold;text-align: right;;">RESULT</td><td style="font-weight: bold;text-align: right;;">Manager</td><td style="font-weight: bold;text-align: right;;">Exclude</td><td style="font-weight: bold;text-align: right;;">Help1</td><td style="font-weight: bold;text-align: right;;">Help2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: center;;">1</td><td style=";">Ryan Smith</td><td style=";">Steve Fowler</td><td style=";">Ryan Smith</td><td style=";">Steve Fowler</td><td style=";">Bobby Henderson</td><td style=";">Steve FowlerSteve Fowler</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;;">2</td><td style=";">Jack Brown</td><td style=";">Steve Fowler</td><td style=";">Jack Brown</td><td style="text-align: right;;"></td><td style=";">James Windpipe</td><td style=";">Steve FowlerSteve Fowler</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;;">3</td><td style=";">Tom Judge</td><td style=";">Steve Fowler</td><td style=";">Tom Judge</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Steve FowlerSteve Fowler</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;;">4</td><td style=";">Harry Wilcox</td><td style=";">Steve Fowler</td><td style=";">Harry Wilcox</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Steve FowlerSteve Fowler</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: center;;">5</td><td style=";">James Windpipe</td><td style=";">Bobby Henderson</td><td style=";">Michael Rooney</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: center;;">5</td><td style=";">Michael Rooney</td><td style=";">Steve Fowler</td><td style=";">Harry Sherwood</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Steve FowlerSteve Fowler</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: center;;">6</td><td style=";">Harry Sherwood</td><td style=";">Steve Fowler</td><td style=";">Brad Hislop</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Steve FowlerSteve Fowler</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: center;;">7</td><td style=";">Brad Hislop</td><td style=";">Steve Fowler</td><td style=";">Steve Fowler</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Steve FowlerSteve Fowler</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: center;;">8</td><td style=";">Steve Fowler</td><td style=";">Jose Jepro</td><td style=";">Rio Vidic</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Steve FowlerJose Jepro</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: center;;">9</td><td style=";">Rio Vidic</td><td style=";">Steve Fowler</td><td style=";">James Eastwood</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Steve FowlerSteve Fowler</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: center;;">10</td><td style=";">James Eastwood</td><td style=";">Steve Fowler</td><td style=";">Barry Gerrard</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Steve FowlerSteve Fowler</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: center;;">11</td><td style=";">Barry Gerrard</td><td style=";">James Eastwood</td><td style=";">Nemanja Terry</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Steve FowlerJames Eastwood</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: center;;">12</td><td style=";">Nemanja Terry</td><td style=";">James Eastwood</td><td style=";">Phil Smalling</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Steve FowlerJames Eastwood</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: center;;">13</td><td style=";">Phil Smalling</td><td style=";">James Eastwood</td><td style=";">Alan Rashford</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Steve FowlerJames Eastwood</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: center;;">14</td><td style=";">Alan Rashford</td><td style=";">James Eastwood</td><td style=";">Andy Irwin</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Steve FowlerJames Eastwood</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: center;;">15</td><td style=";">Andy Irwin</td><td style=";">Phil Smalling</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Steve FowlerPhil Smalling</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: center;;">16</td><td style=";">Jimmy Vialli</td><td style=";">James Windpipe</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">C2=$F$2,C2=$F$3</font>),"",$E$2&C2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">SEARCH(<font color="Green">$E$2,G2</font>)</font>),1,""</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$D$2:D2</font>)>COUNT(<font color="Red">H:H</font>),"",INDEX(<font color="Red">$B$2:$B$18,SMALL(<font color="Green">IF(<font color="Purple">$G$2:$G$18=$E$2&$C$2:$C$18,ROW(<font color="Teal">$A$2:$A$18</font>)-ROW(<font color="Teal">$A$1</font>)</font>),ROWS(<font color="Purple">$D$2:D2</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

ryansm05

Board Regular
Joined
Sep 14, 2016
Messages
127
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

Well-known Member
Joined
Jul 15, 2016
Messages
607
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

Board Regular
Joined
Sep 14, 2016
Messages
127
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

Well-known Member
Joined
Jul 15, 2016
Messages
607
Is your goal the same as saying list all employees, except for employees that have Bobby Henderson or James Windpipe as Line managers?
 

Forum statistics

Threads
1,078,227
Messages
5,338,965
Members
399,272
Latest member
jakepenner

Some videos you may like

This Week's Hot Topics

Top