I need drastic help here! I've been trying to write out the logic for this and I can't seem to get this even down on paper, let alone write the code. So here it goes:
I have 9 columns. Column 1 contains the highest level user (the CEO of a firm). In column 2, we have all the CEO's subordinates, in column 3 you have all the subordinates' subordinates and so forth. The problem is as follows:
There are a total of 4000 rows where in column 1, you have the CEO's name listed 4000 times. When I do a filter and look at the filtered list of people, we have 10 direct reports. When I select the first direct report (call him A) to the CEO, I then look at the third column and A's direct reports are shown. If I select the first direct report to A in the third column, I can look at the 4th column and I have a set of direct reports, and it keeps going down till column 9. However, there are instances where if I selects the second direct report to the CEO (call him B), he has less direct reports shown in the 3rd column and if I keep filtering down, the last child node or direct report ends at column 5 and not 9.
I'm trying to build a heirarchy of all these parent child relationships. I figured that if I have 3 variables: Manager, Level_Number, and Position and somehow store each of the 3 variables in to a collection, I can essentially have 1 massive collection that includes all the names I need to get.
So for example, If the CEO (Manager = 0, Level_Number = 1, and Position = 1) has 10 direct reports. Each direct report will be as follows:
M = Manager, Level_Number = L, Position = 1
CEO = M0L11
Direct Report #1 = M1L1P1
Direct Report #2 = M1L1P2
Direct Report #3 = M1L1P3
Direct Report #4 = M1L1P4 ...
Direct Report #10 = M1L1P10
Now if I look at the third column and just look at Direct Report #1 and notice he has 3 direct reports, I know that the reference will be:
Direct Report #1 = M1L2P1
Direct Report #2 = M1L2P2
Direct Report #3 = M1L2P3
Similarly, for the Direct Report #2 to the CEO will be (if he also has 3 direct reports):
Direct Report #1 = M2L2P1
Direct Report #2 = M2L2P2
Direct Report #3 = M2L2P3
Now, each ID (i.e. M#L#P#) can be stored as a key in a collection referring to the item as the name of the person I'm saving. Can someone PLEASE help me figure a way to have 1 collection with each direct report STORED with an ID as the key in a collection in the format M#L#P#?
I've been going nuts trying to do this and I'm tired of trying to figure this out. My midyear reviews are coming up and if this doesn't seem to work, I'm not going to look good whatsoever! Its due in 3 days for me and I'm starting to get super worried.
If someone could either write out the logic or even the code to this thing, I swear, I will consider you a guru of all times...(and a possibility for a 6 pack of beer) :)
