Org Chart Design Help

[rhexcel]

Hello!

I'm hoping this amazing forum can help me to find an easy solution to an org chart problem. I have a list of names of 100 employees in my department in column A. In column B I have a list of that employee's manager within the department. There are several levels of managers but I'm hoping to find a formula for column C that returns one of 6 Associate Vice Presidents that each employee reports to directly or whom their manager's manager or manager's manager's manager reports to. The only higher level than those AVPs is our CEO. For those 6 AVPs, their own name can be returned, not "Monique Johns" the hypothetical CEO in this case. I have attached a hypothetical spreadsheet with 10 employees and only 2 AVPs in the example. Is there a formula to help reference the "Reports To" column until it lands on a manager who is one of the 2 AVPs? Thank you!

[AliGW]

This works if the two in yellow are the only ones we need to worry about, but it doesn't match your expected results, so either they are wrong or there's something you're not telling us:

=IF(B19="Monique Johns",A19,IF(INDEX($B$19:$B$28,MATCH(B19,$A$19:$A$28,0))="Monique Johns",B19,INDEX($B$19:$B$28,MATCH(B19,$A$19:$A$28,0))))

[Croweater]

There will be a formula, but depending on how many levels of manager you have it will get long and complex (also it may not work if you introduce extra levels of management)

So I wrote a macro, that should work regardless of how many levels you have, and how many AVPs.

Please Login or Register  to view this content.

Let me know if you need help implementing the macro, but essentially all you need to do is press alt+F11 and paste the code into that window. Then you can run the macro whenever you want from the Excel menus.

In the attachment I've associated the code with a button click.

[rhexcel]

Thank you, AliGW. I'm in awe of how quickly people here can think up these formulas. I think where the problem comes in is that some people are 1, 2, 3, or 4 people "away" from "Monique Johns." So as Croweater as pointed out below, that may cause a formula to get very long. Basically I need something that says, "look in the "Reports To" field and successively return the person in the reporting chain until it reaches Monique Johns minus one level." I feel that this is very close those. Thank you so much for your kind help.

[rhexcel]

Thank you so much, Croweater. I was able to bring this fabulous macro into my spreadsheet. For some reason it doesn't work and it seems to be returning random names in the AVP field that are not even in the person's reporting chain. I'm going to work on making sure my spreadsheet exactly matches yours just in case I've done something wrong on my end. Thank you again. The button is great!

[Croweater]

Don't forget that the macro assumes the employees start at Row 19, so if your employee list starts in a different row, you will need to change StartRow in the macro.

Failing that you can put up your spreadsheet that isn't working and I can have a look at it. (You may want to change the names).

If that is too much work, just show me a sheet with a couple of examples of the 'management chains' where the macro is going wrong.

If the employee names are not EXACTLY the same (in the reports to column), it will not find them (in the employee name column) and the chain will be 'broken'. This includes extra spaces.

So, if that is the case the trim function can be used to still find these employess.

Please Login or Register  to view this content.

[rhexcel]

Thank you Croweater,
I did change the first row. I also tried change the names to initials and rerun the macro and it now just freezes. So I'm attaching here to see if you can tell what is wrong. I've put a couple of answers of what the answers should be. Basically looking successively at who each person reports to until "MT" (CEO) is reached and returning the person that reports to her (1 of 6 AVPS). Thank you!

[Croweater]

OK. One reason it didn't work for you is because you changed the way you put it all together. The way it works out who the AVP is if the person they report to is not found in the employee list. This is the way you set it up in the first place.

That all fell apart when you decided to put the CEO in the list and made them report to themselves!

The second reason, is that although you changed the start row, you didn't change the employee range in this line (this is my fault);

brow = Application.Match(Trim(Range("B" & arow).Value), Range("A19:A9999"), 0)

So I have changed the code somewhat to look for the CEO (the one who reports to himself) and then define an AVP as one who reports to the CEO.
I have also changed it so that if you change the start row again, you only have to change it once.
Also, just so it doesn't ever get caught in a loop, if you have a 'circular reporting' (a reports to b and b reports to a), it will find this for you.

You'll notice in the attachment, I also did a 'sanity check', a couple of columns to check that the person who it picked out as the AVP in each row actually reports to the CEO.

Edited Note: If you change the line I highlighted above to the range you changed the employees to ie.

brow = Application.Match(Trim(Range("B" & arow).Value), Range("A2:A9999"), 0) AND removed MT reporting to MT in the first line (Made both those cells blank), you will get the same AVP list as for the code below.

Please Login or Register  to view this content.

Just as an aside....this formula =SUMPRODUCT(1/COUNTIF(C3:C96,C3:C96)) will show you how many AVPs you have and it is 7 not 6.

They are, DL, AH, SW, SH, JP, CS and MM.

[Croweater]

I also worked out your full org chart with this macro;

Please Login or Register  to view this content.

It puts an asterisk (*) at the end of each reporting stream as a separator.

See attached.

[rhexcel]

Thanks Croweater! This is amazing and I will refer to it often. We have all kinds of org charts but nothing that lists people out by AVP. So helpful and I don't know how I can repay you..but sending a virtual bottle of the finest, most expensive wine there is!

[Croweater]

LOL. A virtual bottle of Penfolds Grange 2002 would do very nicely!

Thank you for the gesture and I'm always happy to help those who appreciate it. Cheers!