multi level relationship challenge

[Jaimarti]

Hi, I am new to the Forum and look forward to your feedback. I am using EXCEL 2007 but am open to other options. I am familiar with using VBA. I am having an issue getting my head around what to do to accomplish the following. I have two Columns of data that represent a parent child relationship and each child may have siblings as well as be a parent. All information is built based on the two columns of data. Here is simple example of data. I am not sure how many generations of data there is I have seen up to twenty. But it is hard to know until I can get this done. The output of the exercise is to be able to output a list of dependencies. </SPAN>

Data Example</SPAN>
Parent Child </SPAN>
AZPHNX-DEVILS MOUNTAIN, AZPHNX-I-17 & KACHINA VILLAGE </SPAN>
AZPHNX-DEVILS MOUNTAIN, AZPHNX-VOLUNTEER MOUNTAIN </SPAN>
AZPHNX-VOLUNTEER MOUNTAIN, AZPHNX-I-17 & EXIT 236 </SPAN>
AZPHNX-MOUNT ELDEN, AZPHNX-DEVILS MOUNTAIN </SPAN>
AZPHNX-MOUNT ELDEN, AZPHNX-TWIN ARROWS</SPAN>

Tree view of example </SPAN>
Currently there are 5000 Rows of data. I do not need to build an association for every Parent Child etc. relationship at once. I would like to right click on a parent name select build dependencies list( I know how to do the right click and add to menu portion)and execute the code to run through the 2 columns of data and build basically the family tree down to the furthest relationship. I do not have a current need to look the other way to build an ancestry for the parent selected. I have tried several different methods but end up crashing Excel, only first level relationships or missing relationships. I know that there must be a simple solution but it eludes me for the present. </SPAN>

 

[coldsteelmike]

Hmmm.

You're dead on with the logical constraints.

As for the output, I'd much prefer the tree to read left-to-right with the Parent, and then the Children.

With your time and energy in mind though, I think I should be able to make due with the reverse.

The coup de gras would be the parent/child lineage on a single row for each parent, and to have it loop through each parent in the column for the FULL list, as opposed to the single input. The latter of which seemed like it'd be an easy putt, but has been driving me absolutely nuts!

Again, thanks for the time. I will most definitely pay it forward.

Mike

 

[Jerry Sullivan]

The coup de gras would be the parent/child lineage on a single row for each parent, and to have it loop through each parent in the column for the FULL list, as opposed to the single input. The latter of which seemed like it'd be an easy putt, but has been driving me absolutely nuts!

Mike, It would be a relatively easy putt to modify the code to provide an unordered dependency list for each parent in a single row. That would only require stepping through each Parent using the main function in Post #8.

The complexity arises in trying to represent the branching that occurs when there are multiple siblings. What would the row look like for Parent "AZPHNX-MOUNT ELDEN" in your ideal scenario?

 

[coldsteelmike]

Jerry,

I was not clear. I have differentiated with the verbiage in the header row. I think it's best we use the "Generation" terms, as opposed to the Children.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 117"]
<tbody>[TR="class: grid"]
[TD="width: 117"]Parent[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 138"]
<tbody>[TR="class: grid"]
[TD="width: 138"]Generation 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 166"]
<tbody>[TR="class: grid"]
[TD="width: 166"]Generation 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 200"]
<tbody>[TR="class: grid"]
[TD="width: 117"]AZPHNX-MOUNT ELDEN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD="width: 138"]AZPHNX-DEVILS MOUNTAIN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 250"]
<tbody>[TR="class: grid"]
[TD="width: 166"]AZPHNX-I-17 & KACHINA VILLAGE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 250"]
<tbody>[TR="class: grid"]
[TD="width: 166"]AZPHNX-VOLUNTEER MOUNTAIN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 200"]
<tbody>[TR="class: grid"]
[TD="width: 117"]AZPHNX-MOUNT ELDEN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 200"]
<tbody>[TR="class: grid"]
[TD="width: 138"]AZPHNX-TWIN ARROWS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Geez, that's not clear...Let's see.

 

[Jerry Sullivan]

Mike, How about something like this...

Excel 2013
	A	B	C	D
20	Parent	Generation 1	Generation 2	Generation 3
21	AZPHNX-MOUNT ELDEN	AZPHNX-DEVILS MOUNTAIN	AZPHNX-I-17 & KACHINA VILLAGE
22	AZPHNX-MOUNT ELDEN	AZPHNX-DEVILS MOUNTAIN	AZPHNX-VOLUNTEER MOUNTAIN	AZPHNX-I-17 & EXIT 236
23	AZPHNX-MOUNT ELDEN	AZPHNX-TWIN ARROWS
Sheet2

It's a variation on the result I proposed in Post #30. Instead of showing the Parent lineage of each unique item, each row would represent the entire branch of each node.