Generate Organization structure for one person use input file of employees

[welchs101]

Hi,

I posted a question a few days ago but it was too broad of a question so i am now trying to narrow my question.

What i am trying to do is generate a organization structure from an input file based on a "name" selected by the user.

I am enclosing an excel file where it is assumed that the user selected "DavidH" to find out about his organization structure. What i need to figure out is shown on the 2nd sheet in the file.

My example file has only a few names in it but my real file has 30,000 names in it.

On the first sheet there are a few columns. First column is employeeID and 2nd col is corresponding employee name. 3rd col is the supervisorID for that employee and 4th col is the supervisor name for that employee.

Issue: Best way to go through list of names to generate output. For some reason i am just not sure how to go through list of names.......do i do it serially or is there anything i can do in parallel to speed up the program. The only way i can see to do this is "brute force" ..ie...but this will make the program take a really really long time.

Any ideas on how to do this would be greatly appreciated........never had to go through a list like this.

if you have questions or are confused about something please let me know and i will do my best to answer.

[welchs101]

the more i am reading i think i need something called a "recursive" loop or something........not sure

[nilem]

Hi welchs101,
try

Please Login or Register  to view this content.

[xlbiznes]

Is your file missing the level of the staff ?

oops sorry , i overlooked the data.

[welchs101]

nilem,

that looks good. i am going through the code now trying to understand what your doing. thanks. I did have a question.

can you explain the procedure "recursErtert" in a litte more detail.

i did not even think about concatenating values to an item in a dictionary.......interesting.

thanks again!

[welchs101]

also i noticed you made a reference to the "microsoft scripting runtime" is this just for the dictionary reference?

[nilem]

"recursErtert" is an example of a recursive procedure that calls itself each time with new parameters ("k" is the key of a dictionary, "n" - row of cells, "m" - column of cell)
"microsoft scripting runtime" is this just for the dictionary reference?
Yes, you're right. The question for you: what is the difference between early and late binding?

[welchs101]

early vs latebinding.........i think you explained this one time before.

early binding is where you set the references in the menu
late binding is where you create objects during the program execution.

is there a benefit to one vs the other.....i guess this is another question........perhaps i should start another thread for this.....i will start another thread for this.

i always though latebinding was better since this way if i give macro to someone else to run i thought they had to make sure that all the appropriate references were checked off......or are the references set by the macro itself?

[welchs101]

looking now more at the recursive procedure............i am probably going to have more questions about this.

[xlbiznes]

Hi,

this is another solution using the tree view control.

[welchs101]

xlbiznes, i click on say cell b6 for "DavidH" and i then click on the "org chart" button and i get a runtime error.....am i doing something wrong?

[xlbiznes]

@welchs101,

can you describe the error message.

[welchs101]

here is a picture of the error message

error_message.JPG

[welchs101]

when i hit debug it highlights in yellow this step in the code

Please Login or Register  to view this content.

[xlbiznes]

I guess the treeview control is missing , can you add this to your sheet as shown from the image below :
treeView.JPG

Under the developer tab-> Insert->ActiveX Controls->More Controls.

AddControl.JPG

[welchs101]

xlbiznes, i will check to see if this tree control was added or not. But before i do question. I see from your post how to "add" it..........once i do add it if i dont like it how to "undo" or "un-add" the additional control? Do you know?

[xlbiznes]

You can simple delete the control and remove the reference from the project references option.

[welchs101]

i wanted to thank you all for your help on this issue. I could not have solved this without you all.

nilem, i ended up using your suggestion/code and it worked great. it took me a while to kinda understand the "recursive" stuff going on.

xlbiznes, thanks for you code as well.