Solved

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

Posted on 2014-11-13
19
986 Views
Last Modified: 2014-11-15
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

0
Comment
Question by:Wangstaa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 2
  • +1
19 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40440200
Something like the attached.
ClosestPoints141113.xlsx
0
 

Author Comment

by:Wangstaa
ID: 40440258
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40440268
>> I have 9K+ data sets for each table.

That would have been helpful to point out initially.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:Wangstaa
ID: 40440279
I did say 2 huge data sets..
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40440318
Please try this.
ClosestPoints141113-2.xlsx
0
 

Author Comment

by:Wangstaa
ID: 40440352
Hi Phillip,

It's close, I need the correlating unique identifier, not the distance.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40440375
Please find attached.
ClosestPoints141113-3.xlsx
0
 

Author Comment

by:Wangstaa
ID: 40440391
Can you show the ID without needing the distance data?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40440405
Could you have just hidden the distance data,

Here it is.
ClosestPoints141113-4.xlsx
0
 

Author Comment

by:Wangstaa
ID: 40440456
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40440475
Ctrl+Shift+Enter. It works for me - I assume it works for you when you open the spreadsheet.
0
 

Author Comment

by:Wangstaa
ID: 40440479
Every time I do ctrl shift enter, the result changes
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40440482
That's because I'm using RANDBETWEEN for the co-ordinates. Use proper co-ordinates if you want them to stop changing.
0
 

Author Comment

by:Wangstaa
ID: 40440627
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
 
LVL 22

Accepted Solution

by:
Ejgil Hedegaard earned 450 total points
ID: 40441389
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40441407
^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
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 50 total points
ID: 40442145
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
 

Author Closing Comment

by:Wangstaa
ID: 40443842
great solution, thank you

Can someone care to explain what does the index do?
0
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 40444938
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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,…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question