Multi-Level reporting

I have a 2-column employee file. Column A has the employee id and column B has the manager's employee ID. The reporting layers can get pretty deep. In this example, there are 6 layers of reporting. For example, Employee id 1200 (which is not in column A) has 2 employees. One of them (1712) has several employees, one of whom (1680) has employees reporting to them and so on for several levels.

Is there a way in Excel (2003 or 2007) to summarize the reporting structure? For instance, I need to know all of the employees that report up through emp id 1712 - not just that report directly to 1712, but all those under 1712's tree.

Thanks, Jack

Rylo,

I hate it when you ask tough questions like what I want as output - I really hadn't given much thought to that. First off, what you have provided so far is terrific!!! What we actually need to see is for a given manager, everyone who reports up to them. Your code shows me from the employee perspective up to top management, and that's helpful. However, we are trying to resolve a problem, where a summary report is showing that a specific manager only has 80 people under him, when he should show over a hundred.

I can picture the output in an Organization Chart format, but am having trouble picturing it in Excel. I need some way to show that Mgr 1712 has 5 direct reports, that 1 of them have people under him and so on. After applying your macro, there should be some way to go back for each employee and summarize how many times they appear in the new columns that your macro creates. I will work on this over the weekend, but any help you can provide would be greatly appreciated.

Jack

Jack

I think there is a way to do this without code.

Use the advanced filter to extract a list of unique values from column B and put it somewhere (say column H).

Then use the countif function to count the number of appearances of that code in column B. This should give you a list of the direct reports for each "manager" from column B.

This is based on the data you have given where each employee / manager combination only appears once.

HTH

rylo

Rylo,

Thanks for your help on this and sorry it has taken me 4 days to get back to you on it. I ran your macro against my test file and it built the hierarchy. I then used advanced filter to create a column of unique Manager IDs. Finally, I used COUNTIF against this set of Manager IDs and the hierarchy and it produced the count I was looking for. I think this is going to get me the result I need for my customer.

I am trying to apply this to my customer's actual spreadsheet. However, it is 18221 rows of data, with lots of other columns. I created a new sheet with just the 2 pertinent columns (Employee ID and Manager ID) and I started the macro against this sheet. However, it has been running for over 2 hours and is still not done. I will let it run a while longer and see if it gets me the results I am looking for.

Jack

Jack

Don't think it should take that long. Can you turn off the auto calculation and see if that improves things.

rylo

Rylo,

I found out that the reason it was running so long was because it was looping because the CEO is listed as reporting to himself. I wanted to modify the macro to use an IF statement to skip the logic if the value in column A matched the value in column B. Unfortunately, my VB skills are lacking and I couldn't get the syntax correct. So, instead, I found his record and in column B, I put CEO instead of his own ID. It ran about 20 minutes to fill in the hierarchy. I added the column of unique manager IDs and then the COUNTIF statements and I have exactly what I was looking for.

Thanks for all of your help. If you have a minute, can you let me know the syntax for adding an IF statement to skip the logic if the value in col A matches the value in col B.

Thanks,
Jack

Jack

In this case I'd put the brake on the do loop statement rather than using an IF statement. Change it to be

Please Login or Register  to view this content.

rylo

Hi Rylo

I know this isnt the place to put this message, but not sure how to get in contact with you. I'm also based in Brisbane and would like to discuss a business issue with you. Please check your private messages for my contact details.. many thanks ...

Peter

Originally Posted by rylo

Jack

In this case I'd put the brake on the do loop statement rather than using an IF statement. Change it to be

Please Login or Register  to view this content.

rylo

Thanks Rylo,

You have been a huge help!!!

Jack