Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hierarchy table
When trying to setup a Hierarchy table to document an organization org chart
- how do I enter the data in excel to show A is over B and C is under B and A? I also need to find a method to extract this data and have it create a org chart or export it based on the layout to a .CSV file to be extracted to a program that will read the data based on the hierarchy I decide to pull. EX: John Smith has Jane Smith & Carl Smith that are under him. I need to type the data in excel where the relationship from Jane & Carl will link back to John Smith . Are there any tools that regardless of when I add a persons name (point in time) will assign the individual to the appropriate group based on a letter or number. 1 = John Smith Therefore when typing in Jane & Carl's name I assign them a status of one and when I pull the data it creates the tree that links them as direct reports to John? I tried outline & grouping - Ewwwwwww (maybe I am doing it wrong) Let me know - Thanks.... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hierarchy table
In an abstract sense, we can represent a hierarchy as a record which
contains the data item (name) plus a series of pointers which can be interpreted to position the data item in the structure. If we consider the level below John, then there could be a number or people (or none) that report up to the higher level and each one of these could have people at a lower level. So, a "down" pointer could identify the first person in the lower level (could be zero or -1 to indicate none), and a "next" or "same" pointer could indicate the next person who is at the same level and who reports to the same individual (again, zero or -1 could mean no more, or end of list). This could be supplemented by a "previous" pointer to indicate the person before at the same level, and an "up" pointer could identify the person above to whom they report, though these two pointers are not strictly necessary to traverse the structure but do help to speed things up. In addition to this, there needs to be some way of identifying the person who is at the top (or the start) of the structure, and in computer terms you would normally have another pointer which indicated where the next data item would be placed (i.e. a "free-space" pointer). So let's see how we can put this into practice - suppose we have a hierarchy like this (apologies if it does not come out looking very good): A B C D E F G H I J "A" has 3 people directly below (B, C and D). B only has one subordinate (E), but C has three (F, G and H) and D has two (I and J). This could be represented in Excel like this: ROW 1 start: 8 2 free: 14 3 Name Down Next Previous Up 4 B 6 9 -1 8 5 F -1 7 -1 9 6 E -1 -1 -1 4 7 G -1 11 5 9 8 A 4 -1 -1 -1 9 C 5 13 4 8 10 I -1 12 -1 13 11 H -1 -1 7 9 12 J -1 -1 10 13 13 D 10 -1 9 8 The various pointers here are pointing to row numbers. Names have been added to this structure in a random order and a program to maintain this structure would have to constantly maintain the pointers as new data items are added or deleted. For this snapshot, the start pointer indicates row 8, or A as being the start of the structure - this points down to row 4 (B), but has a terminator for all the other pointers as there are no other records at this level, and there are no higher levels. B in row 4 points down to row 6 (E) but also across to row 9 (C). There is no previous record as this is the first we have met at this level, but it points up to row 8 (A). Continuing to the record across (row 9, or C), this points down to row 5 (F), further across to row 13 (D), back to row 4 (B) and up to row 8 (A). The record in row 13 (D) points down to row 10 (I), is the last one at this level, points back to row 9 (C) and up to row 8 (A). And so on ... Of course, I have set this up manually to illustrate the points, and as I have indicated before you would need a program to maintain this structure to enable you to add new items, delete existing ones, and to edit the structure (eg if F was to be promoted to a higher level). Anyway, an interesting digression into data structures - I hope this helps. Pete On Oct 30, 8:59*pm, Derrick wrote: When trying to setup a Hierarchy table to document an organization org chart - how do I enter the data in excel to show A is over B and C is under B and A? *I also need to find a method to extract this data and have it create a org chart or export it based on the layout to a .CSV file to be extracted to a program that will read the data based on the hierarchy I decide to pull.. EX: John Smith has Jane Smith & Carl Smith that are under him. *I need to type the data in excel where the relationship from Jane & Carl will link back to John Smith . *Are there any tools that regardless of when I add a persons name (point in time) will assign the individual to the appropriate group based on a letter or number. 1 = John Smith Therefore when typing in Jane & Carl's name I assign them a status of one and when I pull the data it creates the tree that links them as direct reports to John? I tried outline & grouping - Ewwwwwww (maybe I am doing it wrong) Let me know - Thanks.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table hierarchy question | Excel Worksheet Functions | |||
Pivot table totals in low levels of hierarchy | Excel Discussion (Misc queries) | |||
Need ActiveX hierarchy. | Excel Discussion (Misc queries) | |||
Hierarchy in excel | Excel Discussion (Misc queries) | |||
Hierarchy in excel | New Users to Excel |