Hierarchy table

D

Derrick

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....
 
P

Pete_UK

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top