Iterating trig function and removing data over a given limit and returning values....

steve manos2 · Jun 9, 2017

Find all possible TREE (code names) and their DISTANCES (m), within a given Radius (600m), from the center of the subject tree [eg tree 2], and return the codename of all the trees and distances to the subject tree.

The result(s) should be in order of closest (Min) to furthest (Max) from the subject tree as in XL table below. Then repeat the above for each and every subject tree until the Excel table below is populated:

My Problem:
Figuring to employee simple trig to calculate the distances from the X&Y location data (m) and then cast out any values greater than the Radius.

In reality I have 30 000+ Trees in this forest with their locations described in XY coordinate meters this really isn't an issue and figure I could alter VBA code to accommodate any size of population im dealing with but I don’t know how to iterate every tree against the other 29 999 possibilities and then put the resultant(s) into one cell from min to max separated by a semi colon.

I guess I could also write out the resultants into multiple columns and that would work too

... Any ideas folks??

(I tried to paste a markup drawing to make this problem more digestible but gave up after an hour) I can email it if a clearer understanding is needed.


600m
TREE 1	100	6194979	651942
*TREE 2*	75	6193548	653771	TREE 3 (Hyp2) ; Tree 4 (Hyp 1)
TREE 3	60	6195210	652614
TREE 4	90	6195134	652243
TREE 5	25	6193738	654033

<tbody>
[TD="bgcolor: #d6d7ff"]

Tree Name

[/TD]
[TD="bgcolor: #d6d7ff"]

TREE HEIGHT (m)

[/TD]
[TD="bgcolor: #d6d7ff"]

UTM Y(m)

[/TD]
[TD="bgcolor: #d6d7ff"]

UTM X(m)

[/TD]
[TD="bgcolor: #d6d7ff"]

Nearest Tree(s) & Distance(s) meters [based on Radius constant]

[/TD]
[TD="bgcolor: #d6d7ff"]

R (RADIUS)

[/TD]
[TD="bgcolor: #000000"]

[/TD]

[TD="bgcolor: #d6d7ff"]

[/TD]
[TD="bgcolor: #d6d7ff"]