Help! Find Closest Coordinate Match
 

I have two arrays of (x,y) coordinates. Each with roughly 500 pairs. Call
one "Design" and the other "Actual". I need to find the distance from each
Actual coordinate to it's closest Design coordinate. Problems: 1). There is
low probability that any of the Actual coordinates with exactly match the
Design coordinates. 2). The arrays are not ordered anywhere near to
coorespondence (ie. Design coord x1,y1 will not necessarily coorespond with
Actual coord x1,y1). 3). Some Design coordinates will not have any Actual
matches.

Difficult enough? ;)

Answer: Help! Find Closest Coordinate Match
 

Don't worry, I'm here to help! Here's a step-by-step guide on how to find the closest coordinate match between two arrays of (x,y) coordinates in Excel:

1. First, create a new column in your Actual array for the closest Design coordinate. You can label this column "Closest Design".
2. Next, for each Actual coordinate, you will need to calculate the distance to every Design coordinate. You can use the Pythagorean theorem to calculate the distance between two points:
   Code:

   ---

   =SQRT((Actual_x-Design_x)^2+(Actual_y-Design_y)^2)

   ---

3. To calculate the distance between each Actual coordinate and every Design coordinate, you can use the following formula in a new column next to your Actual coordinates:
   Code:

   ---

   =SQRT((Actual_x-Design_x)^2+(Actual_y-Design_y)^2)

   ---

4. Once you have calculated the distance between each Actual coordinate and every Design coordinate, you can use the MIN function to find the smallest distance. In the "Closest Design" column, enter the following formula:
   Code:

   ---

   =MIN(range of distances)

   ---

5. The range of distances should include all of the distances you calculated in step 3 for the corresponding Actual coordinate. For example, if your Actual coordinates are in columns A and B, and your Design coordinates are in columns C and D, the range of distances for the first Actual coordinate would be:
   Code:

   ---

   =$E$2:$E$501

   ---

   , where E2 is the first cell with the distance formula and 501 is the last row with a distance formula.
6. Finally, you can use conditional formatting to highlight any Design coordinates that do not have a match in the Actual array. To do this, select the Design coordinates and go to Home Conditional Formatting New Rule. Choose "Use a formula to determine which cells to format" and enter the following formula:
   Code:

   ---

   =ISNA(MATCH(Design_x,Actual_x,0))

   ---

   where Design_x is the cell with the x-coordinate of the Design coordinate, and Actual_x is the range of x-coordinates in the Actual array. Choose a formatting style to highlight the cells without a match.

That's it! You should now have a column in your Actual array with the closest Design coordinate for each Actual coordinate, and any Design coordinates without a match should be highlighted.

Help! Find Closest Coordinate Match
 

Actually, it's pretty easy to do with an array formula. It'll look something
like this:

{=MIN(SQRT((A3-$E$3:$E$18)^2+(B3-$F$3:$F$18)^2))} Where the column a is
your actual x, b is actual y, e is design x and f is design y. The formula
would go into column c and be copied down through all the actual pairs.
Expand the $e$3:$e$18 and $f$3:$f$18 to fit your entire design array. Use
CONTROL-SHIFT-ENTER when you finish putting in the formula without the
braces. "{}" The braces get added when you hit the control-shift-enter key.

What it does is find the distance from the given actual point to ALL design
points and gets the minumum of them.

Have fun....
Horst

"Bill" wrote:

I have two arrays of (x,y) coordinates. Each with roughly 500 pairs. Call
one "Design" and the other "Actual". I need to find the distance from each
Actual coordinate to it's closest Design coordinate. Problems: 1). There is
low probability that any of the Actual coordinates with exactly match the
Design coordinates. 2). The arrays are not ordered anywhere near to
coorespondence (ie. Design coord x1,y1 will not necessarily coorespond with
Actual coord x1,y1). 3). Some Design coordinates will not have any Actual
matches.

Difficult enough? ;)

Help! Find Closest Coordinate Match
 

Hello Bill,
Flyswift has given a good answer if it is the minimum distance you need.
If you need the coordinates of the nearest 'actual' point then this UDF will
help
I had Design value in A3:B20, Actual values in E3:F20 (but anywhere works
and, of course, the ranges can be longer.
To find the Actual closes to the first Design point I called the UDF with
=Closest(A3:B3,$E$3:$F$20)
This retunes the index of the closest point. If this result is in J3 then
=INDEX(E$3:E$7,$J3) returns the x-value of the closes point while
=INDEX(F$3:F$7,$J3) returns the y-values
The subroutine is not very sophisticated but it works.
+++++++++++++++
Function closest(design As Range, actual As Range)
mymin = 1E+15
mycount = actual.Count
Dim mydesign(2)
ReDim myactual(mycount, 2)
mydesign(1) = design(1)
mydesign(2) = design(2)

k = 1
For j = 1 To mycount Step 2
myactual(k, 1) = actual(j)
myactual(k, 2) = actual(j + 1)
k = k + 1
Next j
mycount = mycount / 2
For k = 1 To mycount
distance = Sqr((mydesign(1) - myactual(k, 1)) ^ 2 + (mydesign(2) -
myactual(k, 2)) ^ 2)
If distance < mymin Then
mymin = distance
myindex = k
End If
Next k
closest = myindex
End Function
+++++++++++++++++++++++++++
If you are new to VBA look at: David McRitchie's site on "getting started"
with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bill" wrote in message
...
I have two arrays of (x,y) coordinates. Each with roughly 500 pairs. Call
one "Design" and the other "Actual". I need to find the distance from
each
Actual coordinate to it's closest Design coordinate. Problems: 1). There
is
low probability that any of the Actual coordinates with exactly match the
Design coordinates. 2). The arrays are not ordered anywhere near to
coorespondence (ie. Design coord x1,y1 will not necessarily coorespond
with
Actual coord x1,y1). 3). Some Design coordinates will not have any
Actual
matches.

Difficult enough? ;)

Help! Find Closest Coordinate Match
 

Bernard,

Thanks for the helpful response.
Your approach was exactly what I was looking for. Although, for some
reason, I did have a problem setting the "mymin" initial value as 1E+15 even
though I dimensioned the variable as "long". I just set it to 10,000
believing that I will not have more than 10,000 coordinate points to deal
with.

Thanks again.

-Bill

"Bernard Liengme" wrote:

Hello Bill,
Flyswift has given a good answer if it is the minimum distance you need.
If you need the coordinates of the nearest 'actual' point then this UDF will
help
I had Design value in A3:B20, Actual values in E3:F20 (but anywhere works
and, of course, the ranges can be longer.
To find the Actual closes to the first Design point I called the UDF with
=Closest(A3:B3,$E$3:$F$20)
This retunes the index of the closest point. If this result is in J3 then
=INDEX(E$3:E$7,$J3) returns the x-value of the closes point while
=INDEX(F$3:F$7,$J3) returns the y-values
The subroutine is not very sophisticated but it works.
+++++++++++++++
Function closest(design As Range, actual As Range)
mymin = 1E+15
mycount = actual.Count
Dim mydesign(2)
ReDim myactual(mycount, 2)
mydesign(1) = design(1)
mydesign(2) = design(2)

k = 1
For j = 1 To mycount Step 2
myactual(k, 1) = actual(j)
myactual(k, 2) = actual(j + 1)
k = k + 1
Next j
mycount = mycount / 2
For k = 1 To mycount
distance = Sqr((mydesign(1) - myactual(k, 1)) ^ 2 + (mydesign(2) -
myactual(k, 2)) ^ 2)
If distance < mymin Then
mymin = distance
myindex = k
End If
Next k
closest = myindex
End Function
+++++++++++++++++++++++++++
If you are new to VBA look at: David McRitchie's site on "getting started"
with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bill" wrote in message
...
I have two arrays of (x,y) coordinates. Each with roughly 500 pairs. Call
one "Design" and the other "Actual". I need to find the distance from
each
Actual coordinate to it's closest Design coordinate. Problems: 1). There
is
low probability that any of the Actual coordinates with exactly match the
Design coordinates. 2). The arrays are not ordered anywhere near to
coorespondence (ie. Design coord x1,y1 will not necessarily coorespond
with
Actual coord x1,y1). 3). Some Design coordinates will not have any
Actual
matches.

Difficult enough? ;)

Help! Find Closest Coordinate Match
 

Just to record a worksheet formula only method to also identify the closest
coord,

=MATCH(1,--(C3=SQRT((a3-$e$3:$e$18)^2+(B3-$f$3:$f$18)^2)),0) as an array
also will return an index into the design array pointing to the closest
point. The formula would be entered into the d column.

Horst

"flyswiftly" wrote:

Actually, it's pretty easy to do with an array formula. It'll look something
like this:

{=MIN(SQRT((A3-$E$3:$E$18)^2+(B3-$F$3:$F$18)^2))} Where the column a is
your actual x, b is actual y, e is design x and f is design y. The formula
would go into column c and be copied down through all the actual pairs.
Expand the $e$3:$e$18 and $f$3:$f$18 to fit your entire design array. Use
CONTROL-SHIFT-ENTER when you finish putting in the formula without the
braces. "{}" The braces get added when you hit the control-shift-enter key.

What it does is find the distance from the given actual point to ALL design
points and gets the minumum of them.

Have fun....
Horst

"Bill" wrote:

I have two arrays of (x,y) coordinates. Each with roughly 500 pairs. Call
one "Design" and the other "Actual". I need to find the distance from each
Actual coordinate to it's closest Design coordinate. Problems: 1). There is
low probability that any of the Actual coordinates with exactly match the
Design coordinates. 2). The arrays are not ordered anywhere near to
coorespondence (ie. Design coord x1,y1 will not necessarily coorespond with
Actual coord x1,y1). 3). Some Design coordinates will not have any Actual
matches.

Difficult enough? ;)

I know this thread is old but I have just found use for it. I had to use these formulas to plot the nearest neighbours of clustered coordinates ('actual') which were enclosed by the coordinates for the outline of a circle ('design'). In such a case i had to use ...

{=MIN(SQRT((abs(A3-$E$3:$E$18)^2)+abs((B3-$F$3:$F$18)^2)))}

so that neighbours behind the coordinate of interest (in the 'actual' column) were also considered.