Find 10 closest x,y coordinates between two huge x,y data set

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	Y
40259	125.050	33.15
40260	126.550	33.15
11613	-3.675	0.00
9837	107.525	0.00
9838	115.150	0.00

Open in new window


Table 2
Unique Name	X	Y
9844	137.050	0.00
9843	142.850	0.00
9842	147.950	0.00
9841	155.575	0.00
33297	159.057	0.00
26345	199.300	0.00
26344	207.300	0.00

Open in new window

WangstaaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Something like the attached.
ClosestPoints141113.xlsx
0
WangstaaAuthor Commented:
Hi Phillip,

I can't do it your way because I have 9K+ data sets for each table.

I need the data to spit out in the following way
Capture.PNG
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
>> I have 9K+ data sets for each table.

That would have been helpful to point out initially.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

WangstaaAuthor Commented:
I did say 2 huge data sets..
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Please try this.
ClosestPoints141113-2.xlsx
0
WangstaaAuthor Commented:
Hi Phillip,

It's close, I need the correlating unique identifier, not the distance.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Please find attached.
ClosestPoints141113-3.xlsx
0
WangstaaAuthor Commented:
Can you show the ID without needing the distance data?
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Could you have just hidden the distance data,

Here it is.
ClosestPoints141113-4.xlsx
0
WangstaaAuthor Commented:
I am not familiar with this part, I couldn't get the same result after pressing enter or ctrl shift enter, what did you use?
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Ctrl+Shift+Enter. It works for me - I assume it works for you when you open the spreadsheet.
0
WangstaaAuthor Commented:
Every time I do ctrl shift enter, the result changes
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
That's because I'm using RANDBETWEEN for the co-ordinates. Use proper co-ordinates if you want them to stop changing.
0
WangstaaAuthor Commented:
I have another problem, sometimes there are multiple points with the same distance, I need those points to be uniquely identified.

Please use the sheet attached below.

Capture.PNGdistance-calculation.xlsx
0
Ejgil HedegaardCommented:
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))

It is standard formulas, not array formulas.

See sheet.
distance-calculation.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Glenn RayExcel VBA DeveloperCommented:
^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.

-Glenn
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Please find attached.

I have added a small distance error (in the order of 0.00000001), which distinguishes the various rows.
distance-calculation2.xlsx
0
WangstaaAuthor Commented:
great solution, thank you

Can someone care to explain what does the index do?
0
Ejgil HedegaardCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.