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.

EMPLOYEE NAME	REPORTING MANAGER	FUNCTIONAL MANAGER
Vinay Chonale	Rohit Jahagirdar	Sunil Bailakere
Rohit Jahagirdar	Sunil Bailakere	Dhananjaya S
Husain Mulani	Vinay Chonale	Rohit Jahagirdar
Neena Murti	Vinay Chonale	Rohit Jahagirdar

<tbody>
</tbody>

Result should be

Manager	No of FTEs
Dhananjaya S	5
Sunil Bailakere	4
Rohit Jahagirdar	3

<tbody>
</tbody>

 

[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.

EMPLOYEE NAME	Designation	REPORTING MANAGER	FUNCTIONAL MANAGER
Vinay Chonale	Team Leader	Rohit Jahagirdar	Sunil Bailakere
Rohit Jahagirdar	Assistant Manager	Sunil Bailakere	Dhananjaya S
Husain Mulani	Team Member	Vinay Chonale	Rohit Jahagirdar
Neena Murti	Team Member	Vinay Chonale	Rohit Jahagirdar
Sunil Bailakere	Deputy Manager	Dhananjaya S	Girish

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

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

Manager	Deputy Manager	Assistant Manager	Team Leader	Team Member
Dhananjaya S	1	1	1	2
Sunil Bailakere	0	1	1	2
Rohit Jahagirdar	0	0	1	2

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

 

[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.