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.

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.