#### Shreesurya

##### New Member
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>

Last edited:

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

#### MickG

##### MrExcel MVP
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
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

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>

 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

##### MrExcel MVP
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
Sorry for the Confusion Mick.

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.

Replies
6
Views
289
Replies
1
Views
266
Replies
1
Views
208
Replies
0
Views
140
Replies
12
Views
141