Reporting Hierarchy - Can it be done?

[sscornav]

I need to create a report of how many contractors and employees report to each of the President's direct reports. The organization has many levels, some of the people may be 5 levels down while others report directly to them. I can't use vlookup because there are many levels. I almost need a vllokup loop, but that is beyond my capabilities. I need help generating the "Group" column. Thanks!

I start with this:

Excel Workbook
	A	B	C
1	Employee	Supervisor	Type
2	Joe	Frank	Employee
3	Sally	Steve	Contractor
4	Mary	Steve	Contractor
5	Jeff	Frank	Employee
6	Lauren	Jeff	Contractor
7	Karen	Jeff	Employee
8	Mike	Joe	Contractor
9	Fred	Frank	Employee
10	Ron	Karen	Contractor
11	Burt	Karen	Contractor
12	Ralph	Kyle	Employee
13	Frank	President	Employee
14	Steve	President	Employee
15	Kyle	President	Employee
Sheet1

I want to use a macro to generate this:

Excel Workbook
	A	B	C	D
1	Employee	Supervisor	Type	Group
2	Joe	Frank	Employee	Frank
3	Sally	Steve	Contractor	Steve
4	Mary	Steve	Contractor	Steve
5	Jeff	Frank	Employee	Frank
6	Lauren	Jeff	Contractor	Frank
7	Karen	Jeff	Employee	Frank
8	Mike	Joe	Contractor	Frank
9	Fred	Frank	Employee	Frank
10	Ron	Karen	Contractor	Frank
11	Burt	Karen	Contractor	Frank
12	Ralph	Kyle	Employee	Kyle
13	Frank	President	Employee	Frank
14	Steve	President	Employee	Steve
15	Kyle	President	Employee	Kyle
Sheet1

So I can generate this from a Pivot:

Excel Workbook
	A	B	C	D
18
19	Count of Employee	Type
20	Group	Contractor	Employee	Grand Total
21	Frank	4	5	9
22	Kyle		2	2
23	Steve	2	1	3
24	Grand Total	6	8	14
Sheet1