Setting up Tree Structure

[jomili]

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.

[zbor]

How about something like this?

[jomili]

Zbor,

Thanks for trying, but that doesn't do it. For instance, if on the "Reports To" tab I pick Barack Obama, I can see that Albert Einstein, Jack Nicholson, and Weird Al Yankovick work for him, and how many hours they worked. But those three are all supervisors; they each have teams of caseworkers who report to them, and it's those caseworkers I want to see, and the hours they worked.

If my spreadsheet has 20K rows, I need to be able to see it like this:

Biggest boss>Smaller boss>Smaller boss>Caseworker>Hours worked
1 of these 30 of these 400 of these Thousands of these

But thanks for trying.

[zbor]

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

[JBeaucaire]

Try this:

Please Login or Register  to view this content.

[blane245]

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

[jomili]

Thanks everyone for all the help, but none of the solutions are working correctly.

JBeaucaire, I like the elegance of your solution, but the results aren't accurate. For instance, line 1 shows this:

Please Login or Register  to view this content.

I'm assuming this is in order of Big Boss>Smaller bosses. Darth Vader doesn't supervise himself.

Also, this line:

Please Login or Register  to view this content.

Frankenstein doesn't supervise anyone.

Blane,
On your spreadsheet you show Thomas Edison in the same column with Abaddon. No one supervises Abaddon. Abaddon supervises Adolf, Adolf supervises Thomas Edison, Thomas Edison supervises a host of folks.

In any solution, Abaddon should be by himself, then everyone else either reports to him or to someone under him. There's got to be a way to do this, but I'm getting stumped. I appreciate you all trying.

[JBeaucaire]

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.

[jomili]

JBeaucaire,
YOU ROCK! I was beginning to lose hope, and you came through! How can I ever repay you? Your solution is elegant, and I can't wait to test it out on the real thing. Thank you so much!

[blane245]

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!

[jomili]

Blane,

I guess you're right. The presentation of yours was kind of confusing to me. If I cut out lines 1-48, and 211-477, of sheet 2 then I have the right structure. It was the extra stuff that was confusing me.

[JBeaucaire]

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

[jomili]

Jerry,

Thanks for offering to take a look again. The original macro you wrote worked great for the hundred or so sample employees I had. Now I'm trying it out on the full list, and I'm hitting errors.
The error I'm getting is a Run-Time error '13", Type Mismatch. The highlighted code is this one:

Please Login or Register  to view this content.

I deleted all of the blanks, thinking that might be causing problems (supervisor with no one to supervise, because they all quit or got hit by a bus, or workers with no supervisor because he got hit by a bus), but that didn't help. I tried various tinkerings without getting any noticeable improvement, so I figured I'd come back to you for help.

In the attached workbook, Sheet4 is the original source data you created the macro for. Sheet1 is the full data minus the blanks. I've randomized the names to keep them private, but was running the macro with the full names, so I don't think the randomization is the problem.


The data in sheet1 covers almost 11000 rows. Before I deleted the blanks there were about 15000. Let me know if you want it and I'll upload the "Full" list including blanks. Thanks in advance for your help on this.

John

[Dulanic]

Only error I see is the fact you put the data on sheet 4, yet his macro refers to sheet 1. If you are going to change the sheet the data is on, the macro needs to be changed.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[jomili]

Dulanic,

Sheet4 is the original source data Jerry created the macro for. Sheet1 is the full data minus the blanks, so is the proper location for the macro to run.

[JBeaucaire]

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.

[jomili]

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.

[JBeaucaire]

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.

[JBeaucaire]

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.

[jomili]

I understand, and put God back in. I know this is a tough problem, so I don't expect anyone to solve it right off the bat. Thanks for trying.

Is there a way to, and should we, take the "Solved" off the thread title, or should I post a new thread?

[JBeaucaire]

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.

[jomili]

Good advice. Thanks for all your help.