Setting up Tree Structure

I receive a weekly spreadsheet with about 20K names. For each name there about 20 columns of information such as Position#, Employee #, Position Function, Job Title, etc. I have to pare this down to send a report to about 30 different Program Directors in 11 different Regions showing who's supervising which caseworkers and how many hours their Caseworkers are using. Caseworker, Program Director, and Supervisor may or may not be in the Position Function or the Job Title, so I can't rely on that. All I can really rely on is the "who reports to whom" column.

The attached example is a way-pared down version of my weekly report. There's a Names column, an Employee ID column, an Hours Worked column, and a Reports to column. I need a macro or a formula that will tell me who works for whom. Ideally it will sort out like this (though there may be in some cases 5 or 6 levels instead of the 3 shown below):

Big boss →Midlevel → Caseworker - hours worked

So results will be something like:

Please Login or Register  to view this content.

So I can create a report to send to Frankenstein showing him that Sigmund and Willey, who are under Plato's supervision, worked umpteen hours each. I'll work out how to paste the data in the format I need, but I need to know how to sort the data to get the structure reflected above.

Any help getting me started would be greatly appreciated.

How about something like this?

How about this... From smaller boss to bigger:

Try this:

Please Login or Register  to view this content.

Here's another try. I will expand every boss branch fully using recursion. Run the macro BuildEmployeetree. Tree Structure.xls

Wow...ONE little letter makes all the difference:

Please Login or Register  to view this content.

Here's the corrected code:

Please Login or Register  to view this content.

Actually, my solution produced boss/employee trees for all bosses. You will see that Thomas Edison has his one tree, while the Abaddon contains everybody. It depends on how you want to represent the relationships. If you like what JBeaucaire did, go with it!

You had a followup question? Post a sample data set here of the new requirements and we'll take a look.

Your new sample data differs from the other in a significant way.

The original data had multiple people that reported to your main person, "Abaddon". All of the people that reported to Abbadon had people reporting to them, and all the people in the list flowed through that secondary list.

In your new data set, "God" is the main person, but only 1 person reports to God. So I tweaked the one error that didn't properly create an array with only 1 item in it, but that only resolved the issue for the one person reporting to God, we DO get that list now. But since no one else reports to God, no other subtrees are created. The tree dies.

That means most of your people are left un-treed.

For the tree to work properly, you need to be able to pick ANY person in column A and follow their "reports to" person sequentially until you run into God within 4 steps.

Here's the tweaked macro so you can run it on your data to see what I mean.

Please Login or Register  to view this content.

I guess the 4 steps is the problem. God is at the top, H56l6g5nst56n,4nn5 reports to God, and then there are probably 5 or 6 other levels down to the bottom level. How do we get past the 4th level?

I deleted God (VERY scary move), and ran the macro again. Got the error message "Run Time error 91 Object Variable or With Block variable not set" at this line

Please Login or Register  to view this content.

My macro isn't going to work, I'm not sure how to approach your data. Look at these samples. Notice the path from first entry employee to God is a different number of steps for different people? Some people it's 6 steps, some 7 and some 8...and that's when I stopped looking. There may be many more levels.

Please Login or Register  to view this content.

Originally Posted by jomili

I deleted God (VERY scary move), and ran the macro again. Got the error message "Run Time error 91 Object Variable or With Block variable not set" at this line

Please Login or Register  to view this content.

You can't delete the zero-level person in the original macro. Everything is flowing backwards from there.

No, I would leave this thread alone. A new thread will garner new contributors and more attention.

Leave this thread closed and start a new one, to keep the mods happy, indicate this is a new need and provide a link back to this original thread.


You should construct a good sample "results" page too, your new data is much more inconsistent, and you should be the one thinking through how it should look with people being 4,5,6,7,8 levels differently.