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>

 

[Jaimarti]

JS411 I spoke to soon it appears to do a max of 48 dependencies. I don't see anything that would cause the limitaion in the code provided above. Any idea whatis caping it.

 

[Jerry Sullivan]

No, I'm not aware of any limit. What are you observing that appears to the result of a limit on the number of dependencies?

 

[Jerry Sullivan]

Doing a quick web search, it appears the limitation will occur when the call stack space runs out of memory to store its local variables.

Recursive Procedures (Visual Basic)

Some threads had simple tests that showed recursive calls exceeding stack space on the order of 4000 calls.

Please let me know what results you are getting and we explore alternatives.


EDIT: I just noticed the linked thread applies to Visiual Studio 2012. I presume VBA has a similar limitation

 

[Jaimarti]

I noticed that as I was running through a few of the bigger chains the results were coming back 48 dependencies. at first I thought it was coincidental but after a few more it was more than that. I will read up on the info provided in the link. thanks

 

[Jerry Sullivan]

So when you ran inputs that would have bigger chains, did the code error and open up the debugger, or did you just notice a pattern of the bigger chains not exceeding 48 dependents?

 

[Jaimarti]

no errors just the pattern of hitting max dependencies of 48. I am checking the Ubound limit when I get a chance to see if I can duplicate. I checked the function loop and it seems to do 4386 loops every time which matches the number of rows. I know an array can only hold around 6553 elements. I am wondering if it is maxing out the array with comparisons</SPAN>

 

[Jerry Sullivan]

I know an array can only hold around 6553 elements. I am wondering if it is maxing out the array with comparisons</SPAN>

Arrays don't have a limit of 6553 elements (please let me know if you're aware of a source where you read that).
If I can try running the code with your data set it might expedite tracking down the problem.

I'll send you a PM with my email address.

 

[Jaimarti]

It is an old limitaion it appears. I site that checked is attached. I guess it has not been alimitaion since 7.0 (Excel 95) Description of the limitations for working with arrays in Excel. Wow I guess I am old to.

 

[Jerry Sullivan]

It is an old limitaion it appears. I site that checked is attached. I guess it has not been alimitaion since 7.0 (Excel 95) Wow I guess I am old to.

Me too, my friend!

BTW the limitation in that thread refers to elements in Arrays in worksheet formulas.
VBA didn't exist back then.

 

[Jerry Sullivan]

Thanks for sending your file with some sample data.

I'm able to generate a dependency list that has more than 48 dependent items.

The problem is simply that the COUNTA formula you're using to display the number of dependents has a fixed range, which caps its value at 48.

If you extend that range reference everything should work fine.