find the way from nodeA to nodeB & its length

[homer24]

Hello,

I guess it is the task for aspiring, as it needs some time to be spend to find solution. Anyway I will take a risk and ask about someone’s help with creating macro for finding the way from pointA to pointB & its distance.

Unfortunately I’m struggling already with that for some time with bad results.
I have attached pdf & excel file.
The excel file consist of a two “data” sheets & one sheet with the layout. The same layout is exported to the pdf for someone who prefers to see pdf instead of excel.
Layout is to just visualize connections of each segments & nodes.

In the excel “coordinate sheet” you will find the data that were used for drawing layout.
Each row there represents just one section with:
• starting point called “NODE_A” & its coordinates (X,Y)
• ending point called “NODE_B” & its coordinates (X,Y)
• the length from NODE_A to NODE_B

When you will join all sections (from above) together you will get the full layout.

The other sheet is called “length to update”.
Each row there consist of NODE_1 & NODE_2 and where I put the question mark it is just required length that I would like the macro to calculate.
The whole problem is that NODE_1 & NODE_2 are just starting & ending points, and in most cases consist of a few sections that sometimes meet junction points somewhere on the way between NODE_1 & NODE_2.

This is difficult for me, as even if I could find the way from NODE_1 to NODE_2 the length is badly calculated because its total include also the length of some “dead end” sections. I mean the sections starting on the junction point but do not leading to the NODE_2.The good news is that there is no any loops in the layout.

I was searching on binary tree algorithm but unfortunately in my case I suppose it is not the issue, as junction points could have more than just two exits (left, right).
Is anyone able to give me any clues or better yet the full vba code, so I could adjust later on to my purposes. Seems that I have already used up all mi ideas…

P.S.
I have raised the question of this problem also in other local forum, see the link below
https://4programmers.net/Forum/Inne/...z_jej_dlugosci
http://www.excelforum.pl/topics15/ma...ci-vt58979.htm

[leelnich]

I'm confused. Many of the nodes listed on length_to_update are not present on coordinate. Without a complete list of segments (directly-connected nodes), how can we traverse the tree?

[homer24]

hmm strange I found them all, have you checked "coordinate" sheet both columns "A" & "D"??

[leelnich]

hmm strange I found them all...

OOPS! A misplaced ")" yielded incorrect test results. Thanks, @homer24.

[karedog]

--deleted--

[leelnich]

Oops! Double post...

[leelnich]

See post #11 for better version- Lee
In this solution, procedure Main calls recursive procedure Traverse to navigate the tree structure.

Please Login or Register  to view this content.

[homer24]

leelnich & karedog you are really great thank you very much, I'm really appreciate!!!!
I have made a short tests of both codes and it seems that both works as it should. The codes are quite short, simple & very quick.
Additionally the one from karedog shows something even more than expected (that could be useful) that is the whole path from node1 to node2.
I will analyze both codes and for sure take opportunity to learn something.
Now I will have to accommodate your codes as it is the part of bigger project.

You have restored faith in humans that they can do something just for fun & help others
You both definitely deserve the virtual case of beer.

[homer24]

how can I close the thread and mark as SOLVED??

[karedog]

--deleted--

[leelnich]

Recursive procedures call themselves, a useful feature when processing dependencies - options that lead to options that lead to options... Although too many recursions could cause memory or stack issues - as pointed out by karedog (in a deleted post) -, if coded and employed with care, such procedures are a simple and effective way to handle smallish data sets like this. Here's a slightly better version of my earlier code:

Please Login or Register  to view this content.

[karedog]

Good job

[homer24]

ok, yesterday I have adjusted leelnich code for my purposes.
I have made it even much shorter as in reality I have never used excel file with "coordinate" & "length to update" sheets.
This file I have attached to the thread have been just prepared to better explain the problem & find solution.

As I said this is the part of bigger project so the data included in that excel file have been already imported from the standard *.txt file to the different tables by other macro I created earlier. So I have moved some parts of the leelnich code (the one for creating Node' collection & Segment's table) to already existing loop that reads to the table the data that you had in the "coordinate" sheet. On the other hand from the loop that pass through the table with data mentioned in the "length to update" I could call now the function for finding the required length (segment by segment).

The code then now looks like below:

Please Login or Register  to view this content.

As I said before I do not expect to have any loops in the layout, additionally as you probably noticed in most cases the node1 is not far away from node2 (sometimes it is even just the same segment or not more than ten). For a testing I have added the nodes that are placed the most far away from each other and didn't have "stack overflow" problem. So I don't think so that recursion call has to go so deep. But it is always good to know the limitation of any method (if such exists).

Anyway I post this thread also on the other excel & vba forums, but there were just You who replied giving any solution.
Now I know where to go and ask for help when any new problem appear in the future. You have saved me a lot of hours spent by computer looking for solution. Although I claim that finding the solution by yourself tastes the best....But this time I had to give up

P.S.
karedog don't feel sad I will deeply analyze your code and for sure use it in the future also

Thanks once again.
Tomasz

[leelnich]

Glad I could help, thanks to homer24 and karedog for the rep!

[Kaper]

Hi Tomasz,
It's a pity you crosposted this thread (I know, you refered to it above, but with no links) without giving references to another forum, like: http://www.excelforum.pl/topics15/ma...ci-vt58979.htm

See Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

I've seen several cases where quite work-consuming solutions were posted independently on different forums :-(

[homer24]

ok, I understand, you are absolutely right, sorry for that. I have updated the first post giving the link to the other forums I posted my Q.
Yesterday I posted there that I found already solution on the other forum to avoid posibility that someone could still working on that, and I thought it was enough