Headcount under Manager

Shreesurya

New Member
Joined
Jul 7, 2014
Messages
42
Hi Experts,

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

Raw Data.
EMPLOYEE NAMEREPORTING MANAGERFUNCTIONAL MANAGER
Vinay ChonaleRohit JahagirdarSunil Bailakere
Rohit JahagirdarSunil BailakereDhananjaya S
Husain MulaniVinay ChonaleRohit Jahagirdar
Neena MurtiVinay ChonaleRohit Jahagirdar

<tbody>
</tbody>

Result should be
ManagerNo of FTEs
Dhananjaya S5
Sunil Bailakere4
Rohit Jahagirdar3

<tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,833
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
How are you getting your totals from your 'Result' table?
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Based on your data the results start "E1".
Code:
[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

New Member
Joined
Jul 7, 2014
Messages
42
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

New Member
Joined
Jul 7, 2014
Messages
42

ADVERTISEMENT

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 NAMEDesignationREPORTING MANAGERFUNCTIONAL MANAGER
Vinay ChonaleTeam LeaderRohit JahagirdarSunil Bailakere
Rohit JahagirdarAssistant ManagerSunil BailakereDhananjaya S
Husain MulaniTeam MemberVinay ChonaleRohit Jahagirdar
Neena MurtiTeam MemberVinay ChonaleRohit Jahagirdar
Sunil BailakereDeputy ManagerDhananjaya SGirish

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

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

Manager
Deputy ManagerAssistant ManagerTeam LeaderTeam Member
Dhananjaya S1112
Sunil Bailakere0112
Rohit Jahagirdar0012

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

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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

New Member
Joined
Jul 7, 2014
Messages
42
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,293
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top