Need to compare XY coordinates from two data sets to find the closest point from one data set compared to the other

Gary1234

New Member
Joined
Apr 1, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I have two data sets, lets call them set A and set B. Both have XY coordinates, the scale of which is micrometres (these are coordinates of individual cells in a 2D microscopy image).

I'm trying to find which cell from set B is closest to each cell in set A, and also get the exact distance they are apart. I.e. I need to compare every single coordinate in set A to every single coordinate in set B, and find the closest from set B to each one in set A.

I've seen this question answered before in the context of latitude and longitude coordinates (see here: Need to compare Latitude & Longitude coordinates to find closest office)

This was a suggested formula for doing this: =INDEX(ACOS(COS(RADIANS(90-$E$3:$E$7)) *COS(RADIANS(90-J3)) +SIN(RADIANS(90-$E$3:$E$7)) *SIN(RADIANS(90-J3)) *COS(RADIANS($F$3:$F$7-K3)))*6371,MATCH(SMALL((ABS(J3-$E$3:$E$7)^2+ABS(K3-$F$3:$F$7)^2)^(0.5),1),(ABS(J3-$E$3:$E$7)^2+ABS(K3-$F$3:$F$7)^2)^(0.5),0))

However, this formula is controlling for the earths radius, which I do not need to do. My XY coordinates are simply points in a 2D space and the difference between each X and Y point is just micrometres.

Does anyone know how I can modify this formula so I can apply this to my data? I'm a complete newb at this, so cannot figure this out!!

I've attached some sample data in an image if that helps.
 

Attachments

  • Trial XY data.png
    Trial XY data.png
    160.4 KB · Views: 11

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,224
Office Version
  1. 2013
Platform
  1. Windows
@Gary1234 Does this help?

Book1
ABCDEFGHIJ
1ABNearest BNearest
2XYRefXYA RefDistanceB Ref
34074.1565.0714326.3536.841141.68805913
44023.2578.4824453560.512191.46271183
54031.4586.9334214.6583.383183.23439223
64180.76134445.01726783
74083.9617.5355135.08779553
84328.8641.4166104.599881
Sheet9
Cell Formulas
RangeFormula
I3:I8I3=MIN(SQRT(((A3-D$3:D$5)^2)+((B3-E$3:E$5)^2)))
J3:J8J3=MATCH(I3,SQRT(((A3-D$3:D$5)^2)+((B3-E$3:E$5)^2)),0)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Solution

Gary1234

New Member
Joined
Apr 1, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
@Gary1234 Does this help?

Book1
ABCDEFGHIJ
1ABNearest BNearest
2XYRefXYA RefDistanceB Ref
34074.1565.0714326.3536.841141.68805913
44023.2578.4824453560.512191.46271183
54031.4586.9334214.6583.383183.23439223
64180.76134445.01726783
74083.9617.5355135.08779553
84328.8641.4166104.599881
Sheet9
Cell Formulas
RangeFormula
I3:I8I3=MIN(SQRT(((A3-D$3:D$5)^2)+((B3-E$3:E$5)^2)))
J3:J8J3=MATCH(I3,SQRT(((A3-D$3:D$5)^2)+((B3-E$3:E$5)^2)),0)
Press CTRL+SHIFT+ENTER to enter array formulas.
Amazing, yes!! That completely solves my problem, thank you so much :D
 

Watch MrExcel Video

Forum statistics

Threads
1,132,645
Messages
5,654,556
Members
418,140
Latest member
ahepple86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top