Headcount under Manager

[Shreesurya]

Hi Experts,

Need your help to calculate number of employees under manager. Is there a way to calculate it?

Raw Data.
[TABLE="class: grid, width: 374"]
<tbody>[TR]
[TD]EMPLOYEE NAME[/TD]
[TD]REPORTING MANAGER[/TD]
[TD]FUNCTIONAL MANAGER[/TD]
[/TR]
[TR]
[TD]Vinay Chonale[/TD]
[TD]Rohit Jahagirdar[/TD]
[TD]Sunil Bailakere[/TD]
[/TR]
[TR]
[TD]Rohit Jahagirdar[/TD]
[TD]Sunil Bailakere[/TD]
[TD]Dhananjaya S[/TD]
[/TR]
[TR]
[TD]Husain Mulani[/TD]
[TD]Vinay Chonale[/TD]
[TD]Rohit Jahagirdar[/TD]
[/TR]
[TR]
[TD]Neena Murti[/TD]
[TD]Vinay Chonale[/TD]
[TD]Rohit Jahagirdar[/TD]
[/TR]
</tbody>[/TABLE]

Result should be
[TABLE="class: grid, width: 234"]
<tbody>[TR]
[TD]Manager[/TD]
[TD]No of FTEs[/TD]
[/TR]
[TR]
[TD]Dhananjaya S[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Sunil Bailakere[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Rohit Jahagirdar[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[lrobbo314]

How are you getting your totals from your 'Result' table?

 

[MickG]

Based on your data the results start "E1".

[COLOR="Navy"]Sub[/COLOR] MG10Mar54
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, -Ac)
    [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn.Offset(, -1) & "," & Dn.Offset(, -2).Value
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] InStr(.Item(Dn.Value), Dn.Offset(, -1)) = 0 [COLOR="Navy"]Then[/COLOR]
            .Item(Dn.Value) = .Item(Dn.Value) & "," & Dn.Offset(, -1)
        [COLOR="Navy"]End[/COLOR] If
       [COLOR="Navy"]If[/COLOR] InStr(.Item(Dn.Value), Dn.Offset(, -2)) = 0 [COLOR="Navy"]Then[/COLOR]
            .Item(Dn.Value) = .Item(Dn.Value) & "," & Dn.Offset(, -2)
       [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn

[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range, S [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, Sp [COLOR="Navy"]As[/COLOR] Variant, S2 [COLOR="Navy"]As[/COLOR] Variant, Sp2 [COLOR="Navy"]As[/COLOR] Variant

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
 Sp = Split(.Item(K), ",")
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] S [COLOR="Navy"]In[/COLOR] Sp
    [COLOR="Navy"]If[/COLOR] .exists(S) [COLOR="Navy"]Then[/COLOR]
       Sp2 = Split(.Item(S), ",")
         [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] S2 [COLOR="Navy"]In[/COLOR] Sp2
             [COLOR="Navy"]If[/COLOR] InStr(.Item(K), S2) = 0 [COLOR="Navy"]Then[/COLOR]
                    Q = .Item(K)
                        Q = Q & "," & S2
                    .Item(K) = Q
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] S2
   [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] S
[COLOR="Navy"]Next[/COLOR] K

Range("E1:F1").Value = Array("Manager", "No of FTE'[COLOR="Green"][B]s")[/B][/COLOR]
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    c = c + 1
    Cells(c, "E") = K
    Cells(c, "F") = UBound(Split(.Item(K), ",")) + 1
[COLOR="Navy"]Next[/COLOR] K

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Shreesurya]

Its Hierarchy kind of thing... Example - Vinay Chonale is reporting to Rohit Jahagirdar and Rohit is reporting to Sunil and Sunil is reporting to Dhananjaya.

so with raw data having only 3 Columns and i am trying to know how many of them reporting to Dhananjaya directly or indirectly.

 

[Shreesurya]

Thank you so much Mick, Macro worked like a Gem

Sorry for the trouble - Is it possible to enhance the macro to add one more column with designation and counts as per designation.

Below is the example of data and result expected.

[TABLE="width: 653"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]EMPLOYEE NAME[/TD]
[TD]Designation[/TD]
[TD]REPORTING MANAGER[/TD]
[TD]FUNCTIONAL MANAGER[/TD]
[/TR]
[TR]
[TD]Vinay Chonale[/TD]
[TD]Team Leader[/TD]
[TD]Rohit Jahagirdar[/TD]
[TD]Sunil Bailakere[/TD]
[/TR]
[TR]
[TD]Rohit Jahagirdar[/TD]
[TD]Assistant Manager[/TD]
[TD]Sunil Bailakere[/TD]
[TD]Dhananjaya S[/TD]
[/TR]
[TR]
[TD]Husain Mulani[/TD]
[TD]Team Member[/TD]
[TD]Vinay Chonale[/TD]
[TD]Rohit Jahagirdar[/TD]
[/TR]
[TR]
[TD]Neena Murti[/TD]
[TD]Team Member[/TD]
[TD]Vinay Chonale[/TD]
[TD]Rohit Jahagirdar[/TD]
[/TR]
[TR]
[TD]Sunil Bailakere[/TD]
[TD]Deputy Manager[/TD]
[TD]Dhananjaya S[/TD]
[TD]Girish[/TD]
[/TR]
</tbody>[/TABLE]

Result Expected (designation columns to be dynamic to add additional designation)

[TABLE="width: 467"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Manager
[/TD]
[TD]Deputy Manager[/TD]
[TD]Assistant Manager[/TD]
[TD]Team Leader[/TD]
[TD]Team Member[/TD]
[/TR]
[TR]
[TD]Dhananjaya S[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Sunil Bailakere[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Rohit Jahagirdar[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

That layout is quite difficult to decipher.
Here are a few of the problems
1) There is no "Girish" in the results
2) What does the Designation " column refer to, column to left or right
3) "Viny Chonale" is a "Manager" and "Employee" (2 columns), so where does he go ???
4) Headers in data and results are rather vaguely matched "i.e. "Functional Manager and "Manager" seem to be the same thing ???

I'm sorry I don't think I can get a result with the data as it stands !!.

 

[Shreesurya]

Sorry for the Confusion Mick.

Below are the answers for your questions. Hope this will help.

1) There is no "Girish" in the results - You may remove that row in the Raw data as well to avoid confusion
2) What does the Designation " column refer to, column to left or right - Its Title for each individual in a company so it would give me no of people in each title the manager has.
3) "Vinay Chonale" is a "Manager" and "Employee" (2 columns), so where does he go ??? He is First Level Manager. i just didn't added in result table to avoid complications. but if he is in result table he will have 2 Team members reporting to him.
4) Headers in data and results are rather vaguely matched "i.e. "Functional Manager and "Manager" seem to be the same thing ??? Employee - First Level Manager (Reporting Manager) - Second Level Manager (Functional Manager)
Headers in Result table is transpose of Designation column in raw data by removing Duplicate.