Org Chart Design Help

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!

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))))

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.

Sub AVP()
Dim lrow As Long
Dim arow As Long

Dim Row As Long
Dim brow As Variant
Dim StartRow As Long

Dim AVPFound As Boolean

    StartRow = 19 'Change to whatever row your employees start at

    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    For Row = StartRow To lrow
        arow = Row
        AVPFound = False
        Do Until AVPFound
            brow = Application.Match(Range("B" & arow).Value, Range("A19:A9999"), 0)
            If IsError(brow) Then
                AVPFound = True
            Else
                arow = brow + StartRow - 1
            End If
        Loop
GotAVP:
        Range("C" & Row).Value = Range("A" & arow).Value
    Next Row
        
End Sub

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.

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.

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

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.

Private Sub CommandButton1_Click()

Dim lrow As Long
Dim arow As Long
Dim AVPRow As Long
Dim Count As Integer

Dim Row As Long
Dim brow As Variant
Dim StartRow As Long

Dim AVPFound As Boolean

    StartRow = 2 'Change to whatever row your employees start at

    lrow = Cells(Rows.Count, 1).End(xlUp).Row

    For Row = StartRow To lrow
        Count = 0
        arow = Row
        AVPFound = False
        Do Until AVPFound
            AVPRow = arow
            brow = Application.Match(Trim(Range("B" & arow).Value), Range("A" & StartRow & ":A9999"), 0)
            arow = brow + StartRow - 1
            If Trim(Range("B" & arow).Value) = Range("A" & arow).Value Then
                AVPFound = True
            End If
            Count = Count + 1
            If Count > 20 Then
                MsgBox ("Circular reporting structure " & Range("A" & arow))
                Exit Sub
            End If
        Loop
GotAVP:
        Range("C" & Row).Value = Range("A" & AVPRow).Value
    Next Row
        
End Sub

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.