I'll admit it... I'm stumped!! Need hierarchy help.

CyrusTheVirus

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
749
Office Version
  1. 365
Platform
  1. Windows
This is the first time in my professional career that I am unable to figure out how to use features/formulas (i've never needed VBA, until maybe now) in order to solve a problem.

Basically i need to create a certain formatted hierarchical structure based on the below table. I need each supervisor to be listed in their parent's list horizontally (like the 2nd table below). If you see Joe, he oversees Paul and Karen who over see other managers, but i only want managers listed if they in fact oversee employees. For instance, Tom and Jerry don't oversee anyone, so they will be excluded from the list.

I've heard this is near impossible to do with formulas, and would be much easier be done by VBA. I'm at a loss... so, who can help with this one? Please let me know of any questions.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Supervisor[/TD]
[TD]Employee[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Paul[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Karen[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Jerry[/TD]
[/TR]
[TR]
[TD]Karen[/TD]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Karen[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]Karen[/TD]
[TD]Terry[/TD]
[/TR]
[TR]
[TD]Karen[/TD]
[TD]Sam[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Andrew[/TD]
[/TR]
[TR]
[TD]Terry[/TD]
[TD]Samantha[/TD]
[/TR]
</tbody>[/TABLE]


Need it to turn into...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Supervisor[/TD]
[TD]Oversees Manager[/TD]
[TD]Oversees Manager[/TD]
[TD]Oversees Manager[/TD]
[TD]Oversees Manager[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Paul[/TD]
[TD]Karen[/TD]
[TD]Mary[/TD]
[TD]Terry[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Karen[/TD]
[TD]Mary[/TD]
[TD]Terry[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Terry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
MickG said:
I can't duplicate your results , see the example file below:-
https://app.box.com/s/1mw9q23n0g2a9qvs2f36j9xcbz3grnwn
Click to expand...

This is VERY close, but it appears that the code executes based on a certain sort... for instance below is my actual data of how it will typically be laid out. The blank employees indicate that they are not managers. Would you be able to adjust the code for the below??

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Supervisor[/TD]
[TD="width: 64"]Employee[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD="align: right"]113[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD="align: right"]136[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD="align: right"]160[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD="align: right"]165[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]103[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]103[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]105[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]107[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]108[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]108[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]108[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]109[/TD]
[TD="align: right"]116[/TD]
[/TR]
[TR]
[TD="align: right"]109[/TD]
[TD="align: right"]158[/TD]
[/TR]
[TR]
[TD="align: right"]109[/TD]
[TD="align: right"]170[/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]112[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD="align: right"]117[/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD="align: right"]138[/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD="align: right"]144[/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD="align: right"]146[/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD="align: right"]164[/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]113[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]114[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]114[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]114[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]114[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]114[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]114[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]114[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]114[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]114[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]114[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]114[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]115[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]115[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]117[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]117[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]117[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]117[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]117[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]117[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]117[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]117[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]117[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]117[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]119[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]120[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]121[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]121[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]121[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]121[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]121[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]122[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]122[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]122[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]122[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]122[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]122[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]122[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]122[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]122[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]124[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]124[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]124[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]124[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]124[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]124[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]124[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]124[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]124[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]124[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]124[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]124[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]124[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]125[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]126[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]126[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]126[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]126[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]126[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]126[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]126[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]127[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]127[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]127[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]127[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]127[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]127[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]127[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]127[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]127[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]128[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]128[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]128[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]128[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]128[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]128[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]128[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]128[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]128[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]128[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]129[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]129[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]129[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]129[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]129[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]129[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]129[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]129[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]129[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]129[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]129[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]129[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]130[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]130[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]130[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]130[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]130[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]130[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]130[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]131[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]131[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]131[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]131[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]131[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]131[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]132[/TD]
[TD="align: right"]168[/TD]
[/TR]
[TR]
[TD="align: right"]133[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]133[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]133[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]133[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]133[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]133[/TD]
[TD="align: right"]128[/TD]
[/TR]
[TR]
[TD="align: right"]133[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]133[/TD]
[TD="align: right"]139[/TD]
[/TR]
[TR]
[TD="align: right"]133[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]134[/TD]
[TD="align: right"]108[/TD]
[/TR]
[TR]
[TD="align: right"]134[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]134[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]134[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]134[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]134[/TD]
[TD="align: right"]169[/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]136[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]137[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]137[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]137[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]137[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]137[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]137[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]137[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]137[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]138[/TD]
[TD="align: right"]126[/TD]
[/TR]
[TR]
[TD="align: right"]138[/TD]
[TD="align: right"]141[/TD]
[/TR]
[TR]
[TD="align: right"]138[/TD]
[TD="align: right"]142[/TD]
[/TR]
[TR]
[TD="align: right"]138[/TD]
[TD="align: right"]179[/TD]
[/TR]
[TR]
[TD="align: right"]138[/TD]
[TD="align: right"]180[/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]139[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]140[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]140[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]140[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]140[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]140[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]141[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]141[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]141[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]141[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]141[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]141[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]141[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]141[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]142[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]142[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]142[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]142[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]142[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]142[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]142[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]142[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]142[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]142[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]142[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]142[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]144[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]144[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]144[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]144[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]144[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]146[/TD]
[TD="align: right"]104[/TD]
[/TR]
[TR]
[TD="align: right"]146[/TD]
[TD="align: right"]111[/TD]
[/TR]
[TR]
[TD="align: right"]146[/TD]
[TD="align: right"]122[/TD]
[/TR]
[TR]
[TD="align: right"]146[/TD]
[TD="align: right"]127[/TD]
[/TR]
[TR]
[TD="align: right"]146[/TD]
[TD="align: right"]129[/TD]
[/TR]
[TR]
[TD="align: right"]146[/TD]
[TD="align: right"]155[/TD]
[/TR]
[TR]
[TD="align: right"]147[/TD]
[TD="align: right"]107[/TD]
[/TR]
[TR]
[TD="align: right"]147[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]147[/TD]
[TD="align: right"]131[/TD]
[/TR]
[TR]
[TD="align: right"]147[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]147[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]148[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]148[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]148[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]148[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]148[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]148[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD="align: right"]101[/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD="align: right"]102[/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD="align: right"]106[/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD="align: right"]109[/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD="align: right"]119[/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD="align: right"]121[/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD="align: right"]134[/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD="align: right"]147[/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD="align: right"]153[/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD="align: right"]157[/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD="align: right"]162[/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[TD="align: right"]174[/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]151[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]151[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]151[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]151[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]151[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]151[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]151[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]151[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]151[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]151[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]151[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]151[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]153[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]153[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]153[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]153[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]154[/TD]
[TD="align: right"]105[/TD]
[/TR]
[TR]
[TD="align: right"]154[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD="align: right"]154[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD="align: right"]155[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]155[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]155[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]155[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]155[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]155[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]155[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]155[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]155[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]156[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]156[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]156[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]156[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]156[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]156[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]156[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]156[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]156[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]156[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]156[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]156[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]156[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]157[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]157[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]157[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]158[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]158[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]158[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]158[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]158[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]158[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]158[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]158[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]158[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]158[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]158[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]158[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]158[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]160[/TD]
[TD="align: right"]115[/TD]
[/TR]
[TR]
[TD="align: right"]160[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]160[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD="align: right"]160[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]160[/TD]
[TD="align: right"]135[/TD]
[/TR]
[TR]
[TD="align: right"]160[/TD]
[TD="align: right"]145[/TD]
[/TR]
[TR]
[TD="align: right"]160[/TD]
[TD="align: right"]151[/TD]
[/TR]
[TR]
[TD="align: right"]160[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]160[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]160[/TD]
[TD="align: right"]178[/TD]
[/TR]
[TR]
[TD="align: right"]161[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]161[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]161[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]161[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]161[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]161[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]161[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]161[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]161[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]161[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]161[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]161[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]161[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]162[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]162[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]163[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]163[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]164[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="align: right"]164[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]164[/TD]
[TD="align: right"]161[/TD]
[/TR]
[TR]
[TD="align: right"]164[/TD]
[TD="align: right"]166[/TD]
[/TR]
[TR]
[TD="align: right"]164[/TD]
[TD="align: right"]172[/TD]
[/TR]
[TR]
[TD="align: right"]165[/TD]
[TD="align: right"]133[/TD]
[/TR]
[TR]
[TD="align: right"]165[/TD]
[TD="align: right"]154[/TD]
[/TR]
[TR]
[TD="align: right"]165[/TD]
[TD="align: right"]167[/TD]
[/TR]
[TR]
[TD="align: right"]165[/TD]
[TD="align: right"]176[/TD]
[/TR]
[TR]
[TD="align: right"]166[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]166[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]166[/TD]
[TD="align: right"]124[/TD]
[/TR]
[TR]
[TD="align: right"]166[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]166[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]166[/TD]
[TD="align: right"]156[/TD]
[/TR]
[TR]
[TD="align: right"]166[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]166[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]166[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]166[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]166[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]167[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]167[/TD]
[TD="align: right"]114[/TD]
[/TR]
[TR]
[TD="align: right"]167[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]167[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]167[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]167[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]167[/TD]
[TD="align: right"]137[/TD]
[/TR]
[TR]
[TD="align: right"]167[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]167[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]167[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]167[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]167[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]167[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]167[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]167[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]168[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]168[/TD]
[TD="align: right"]163[/TD]
[/TR]
[TR]
[TD="align: right"]169[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]169[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]169[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]169[/TD]
[TD="align: right"]148[/TD]
[/TR]
[TR]
[TD="align: right"]169[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]171[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]171[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]171[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]171[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]171[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]171[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]171[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]171[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]172[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]172[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]172[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]176[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]176[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]176[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]176[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]176[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]176[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]176[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]176[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]176[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]176[/TD]
[TD="align: right"]171[/TD]
[/TR]
[TR]
[TD="align: right"]176[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]176[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]178[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]178[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]178[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]178[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]178[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]178[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]178[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]178[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]178[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]178[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]178[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]178[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]178[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]178[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]178[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Try this for results starting "F1".
NB:- If this code does not produce expected result, please show example of expected result.
Code: 
[COLOR=navy]Sub[/COLOR] MG01Sep54
[COLOR=navy]Dim[/COLOR] Rng         [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn          [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] c           [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Dic         [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Dim[/COLOR] R           [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
   [COLOR=navy]If[/COLOR] Dn.Offset(, 1) <> "" [COLOR=navy]Then[/COLOR]
   [COLOR=navy]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR=navy]Then[/COLOR]
         Dic.Add (Dn.Value), Dn.Offset(, 1)
    [COLOR=navy]Else[/COLOR]
        [COLOR=navy]Set[/COLOR] Dic(Dn.Value) = Union(Dic(Dn.Value), Dn.Offset(, 1))
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]Dim[/COLOR] ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] K [COLOR=navy]As[/COLOR] Variant, p [COLOR=navy]As[/COLOR] Variant, rep [COLOR=navy]As[/COLOR] Range
Dim Kay As Variant, Txt As Long 
 c = 1
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] Dic.keys
    c = c + 1: ac = 6
    Cells(1, ac).Value = "Supervisor"
    Cells(c, ac).Value = K
    Kay = K
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] p [COLOR=navy]In[/COLOR] Dic(Kay)
            [COLOR=navy]If[/COLOR] Dic.exists(Kay) [COLOR=navy]Then[/COLOR]
                [COLOR=navy]Set[/COLOR] rep = Dic(Kay)
                    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] R [COLOR=navy]In[/COLOR] rep
                        Txt = R.Value
                        [COLOR=navy]If[/COLOR] Dic.exists(Txt) [COLOR=navy]Then[/COLOR]
                            ac = ac + 1
                            Cells(c, ac) = Txt
                            Cells(1, ac) = "Oversees Manager"
                        [COLOR=navy]End[/COLOR] If
                        Kay = Txt
                    [COLOR=navy]Next[/COLOR] R
             [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] p
[COLOR=navy]Next[/COLOR] K
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
Hey Mick,

Thanks for the reply and the try again. Doesn't seem to yet do it (the originally was closer). Let me work on another example for you and report back. Thanks for your patience.

Basically though, all the supervisors should be listed once in the resulting table, and the names to the right of them should be managers that they can see. Should be a cascading effect.
 
Upvote 0
Hey Mick,

Below is an example. Though i stopped manually adding oversees managers at supervisor 134. The order of the oversees managers doesn't matter, as long as they are all there. As you see, supervisor 102 is one of the top employees, and therefore can see all managers under her, and their managers, and their managers etc. etc. etc. (I hope i correctly manually added them). Again, this is a cascading thing. Please let me know of any questions.

https://app.box.com/s/uc2yyvfi2h1hzkongamql40ejsgv0z5i
 
Upvote 0
Try this for results starting "D1"
Code: 
[COLOR=navy]Sub[/COLOR] MG02Sep12
[COLOR=navy]Dim[/COLOR] Rng         [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn          [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] c           [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Dic         [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Dim[/COLOR] R           [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR=navy]
For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
   [COLOR=navy]If[/COLOR] Dn.Offset(, 1) <> "" [COLOR=navy]Then[/COLOR]
   [COLOR=navy]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR=navy]Then[/COLOR]
         Dic.Add (Dn.Value), Dn.Offset(, 1)
    [COLOR=navy]Else[/COLOR]
        [COLOR=navy]Set[/COLOR] Dic(Dn.Value) = Union(Dic(Dn.Value), Dn.Offset(, 1))
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]

Dim[/COLOR] ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] k [COLOR=navy]As[/COLOR] Variant, p [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Dim[/COLOR] nRng [COLOR=navy]As[/COLOR] Range
 c = 1
[COLOR=navy]

For[/COLOR] [COLOR=navy]Each[/COLOR] k [COLOR=navy]In[/COLOR] Dic.keys
    c = c + 1: ac = 4
    Cells(1, ac).Value = "Supervisor"
    Cells(c, ac).Value = k
   
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] p [COLOR=navy]In[/COLOR] Dic(k)
            ac = ac + 1
            Cells(c, ac) = p.Value
            Cells(1, ac) = "Oversees Manager"
            [COLOR=navy]If[/COLOR] Dic.exists(p.Value) [COLOR=navy]Then[/COLOR]
                [COLOR=navy]If[/COLOR] nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR] [COLOR=navy]Set[/COLOR] nRng = Dic(p.Value) Else [COLOR=navy]Set[/COLOR] nRng = Union(nRng, Dic(p.Value))
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] p
           
        [COLOR=navy]Do[/COLOR] [COLOR=navy]While[/COLOR] Not nRng [COLOR=navy]Is[/COLOR] Nothing
             [COLOR=navy]Set[/COLOR] Rng = nRng: [COLOR=navy]Set[/COLOR] nRng = Nothing
             [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] R [COLOR=navy]In[/COLOR] Rng
                    ac = ac + 1
                    Cells(c, ac) = R.Value
                    Cells(1, ac) = "Oversees Manager"
                [COLOR=navy]If[/COLOR] Dic.exists(R.Value) [COLOR=navy]Then[/COLOR]
                    [COLOR=navy]If[/COLOR] nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR] [COLOR=navy]Set[/COLOR] nRng = Dic(R.Value) Else [COLOR=navy]Set[/COLOR] nRng = Union(nRng, Dic(R.Value))
                [COLOR=navy]End[/COLOR] If
             [COLOR=navy]Next[/COLOR] R
        [COLOR=navy]Loop[/COLOR]
[COLOR=navy]Next[/COLOR] k
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
Mick,

I think it's official. You. Are. A. Genius.

This is what I was looking for (everything appears good). Wish I had your skillzzzzz. Thanks a lot, this is a HUGE help!!

-C
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Big Request, need help before I pull my hair out.
Replies
5
Views
1K
BrisAdrian
B
D
  • Question Question
help on SUM
Replies
5
Views
6K
mvptomlinson
M
A
  • Question Question
Conditional sum help?
Replies
5
Views
5K
stanleydgromjr
S

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top