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
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)

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
160.4 KB · Views: 173

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
@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.

@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

Replies
1
Views
4K
Replies
4
Views
657
Replies
0
Views
762
Replies
7
Views
505
Replies
9
Views
506

1,196,183
Messages
6,013,914
Members
441,795
Latest member
Lilium

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.

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

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