I have two huge x y coordinate data sets. I need to calculate the distance between each X,Y data point in table 1 to each X,Y data point in table 2 and find out 10 closest points for each coordinate by showing it's unique name identifier.

Calculation formula is easy, sqrt((x1-x2)^2+ (y1-y2)^2))

Any help would be greatly appreciated

Table 1

Unique Name X Y40259 125.050 33.1540260 126.550 33.1511613 -3.675 0.009837 107.525 0.009838 115.150 0.00

To find the distance
For O3, copy to P3:W3.
=SMALL(INDEX(SQRT(($B3-Table1[X])^2+($C3-Table1[Y])^2),,),O2)
To get the Unique Name for multiple points
For E3, copy to D3:M3.
=LARGE(INDEX((SQRT(($B3-Table1[X])^2+($C3-Table1[Y])^2)=O3)*Table1[Unique

To find the distance
For O3, copy to P3:W3.
=SMALL(INDEX(SQRT(($B3-Table1[X])^2+($C3-Table1[Y])^2),,),O2)

To get the Unique Name for multiple points
For E3, copy to D3:M3.
=LARGE(INDEX((SQRT(($B3-Table1[X])^2+($C3-Table1[Y])^2)=O3)*Table1[Unique Name],,),COUNTIF(O3:$W3,O3))

^Brilliant solution, Ejgil. I'd been bangin' my noggin' on this most of the afternoon.

NO POINTS for me, but you'll want to tweak the distance formula so that you can copy across and down. In cell O3, use this instead: =SMALL(INDEX(SQRT(($B3-Table1[X])^2+($C3-Table1[Y])^2),,),O$2)

It changes the reference to the second row of data only.

Index without row and column specification makes an array of the formula results in the entire range.

In the formula
=LARGE(INDEX((SQRT(($B3-Table1[X])^2+($C3-Table1[Y])^2)=O3)*Table1[Unique Name],,),COUNTIF(O3:$W3,O3))
the part (SQRT(($B3-Table1[X])^2+($C3-Table1[Y])^2)=O3) returns True/False (equal to 1/0) for each row in the table, so when multiplied with Table1[Unique Name] the Index function makes a list with the Unique Name numbers where there is an match to O3, and 0 for the others.

0

Featured Post

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

A little background as to how I came to I design this code:
Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,â€¦

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question IDÂ (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (httpâ€¦