Help Required to get Count

[nks1975]

Hi Everyone,

I have joined this forum just today so first of all hello to everyone.

I have a query, i have a list of employees and their supervisors now I want to count how many direct or indirect reportees under each supervisor.
For example as per the below list "1500031" should have 5 employees and 1500035 should have 6 employees, same way 1500075 should have 16 employees.
You are requested please create a macro for me for the same.



Emp ID Supervisor
1500012 1500031
1500015 1500031
1500019 1500031
1500023 1500031
1500027 1500031
1500031 1500035
1500035 1500039
1500039 1500059
1500043 1500059
1500047 1500059
1500051 1500059
1500055 1500059
1500059 1500063
1500063 1500067
1500067 1500071
1500071 1500075
1500075 311121

Regards

[Richard Buttrey]

Hi,

Not sure how you arrive at the counts for the 2nd & 3rd examples since neither contain more than one employee.

List your supervisors in say D2 down (Use Data Filter Advanced Unique to create the list). In E2 copied down enter
=COUNTIF(B:B,D2)

[nks1975]

Hi,
In the 2nd example, 1500035 is supervisor of 1500031 that's why it's count is 6 as all working under 1500031 is also a part of it's team ,similar case is for other example.
IT's like if A1 has 5 employees and A1 is reporting to a2 then a2 should have 6 employees.
I want a macro which can count it for me.

Regards,

[Richard Buttrey]

Well that makes a significant difference. You should have explained that in your first post

So in that case in D2 (and assuming the list is sorted on column B ascending

=MATCH(D2,A:A)-2