Nearest point

[AmirTaj]

So, I have 3 sets of 50 point with x and y coordinate. Each set start from origin (0,0). What I must do is arrange all the points so that 1st point and 2nd point is nearest compared to 3rd,4th and 5th point. After locating 2nd point, I must then find the nearest point to the 2nd point. And so on.

All this 3 sets have different x and y location.

So far I'm using sqrt to make a table and sort thing out. But it is difficult to choose the nearest point with so many numbers on the table. Then I added conditional formatting to highlight smallest value. It seem so clustered and confusing.

So my question is, how to locate nearest point using excel.



p/s- newbie here. please be gentle guys =)

 

[offthelip]

I don't think you need to use sqrt to do what you are trying to do, since finding the point with the minimum distance is the same as finding the point with the minimum squared distance.
I have written some code which basically does what you ask, however I am not sure it is what you want because I am not sure quite whether you want ot exclude points that have been found from the 2nd 3rd 4th searches.
I have assumed the x values are in column b and y values in column c from row 2 to 51

Sub test2()

inarr = Range("B1:c51")
cnt = 0
For i = 2 To 51
   mindis = 1E+16
   mindi = 0
 For j = 2 To 51
  If j <> i Then
    ' calculate square fo distance
        deltax = inarr(i, 1) - inarr(j, 1)
        deltay = inarr(i, 2) - inarr(j, 2)
        distsq = (deltax * deltax) + (deltay * deltay)
        If distsq < mindis Then
         mindis = distsq
         mindi = j
        End If
  End If
 Next j
 Cells(mindi, 4) = cnt
 cnt = cnt + 1
 Cells(mindi, 5) = mindis
Next i

 

[AmirTaj]

Thanks offthelips.

wow. tbh, I only understand half of your line.

after testing it, there is an empty boxes in the sequence. I have no idea why.

yes,i would want to exclude point that have already been found.

and instead of referring to line numbers, can it refer to column A? (the point name will be there)

 

[Rick Rothstein]

Does this macro do what you want (same assumptions as offhelip used)...

[table="width: 500"]
[tr]
	[td]Sub OrderedPairs()
  Range("D2:D51").Value = Range("A2:A51").Value
  Range("E2:E51") = Evaluate("B2:B51*B2:B51+C2:C51*C2:C51")
  Range("D2:E51").Sort Range("E2"), xlAscending
  Columns("E").Delete
End Sub[/td]
[/tr]
[/table]

 

[AmirTaj]

Rick,

It did improve from initial sequence, but still halfway from "correct " sequence.


Please refer image below,
https://ibb.co/bTUURV

red arrow is where it should go next, but it wont.

 

[offthelip]

The calculation you asked for doesn't create the pattern shown in your diagram: the nearest point to point 5 is point 7 not point 6 in your diagram. So it is very difficult to help you

 

[AmirTaj]

diagram above is simulation after using Rick suggestion. I just merely showing the obvious wrong step. Yes, it should be as u said.

Your code is more precise but how can I add line that remove point that already been choosen?

 

[offthelip]

I think this will do what you want:

Sub test2()
Dim flags(1 To 51) As Boolean


inarr = Range("B1:c51")
For i = 1 To 51
flags(i) = False
Next i
cnt = 0
For i = 2 To 51
   mindis = 1E+16
   mindi = 0
 For j = 2 To 51
  If j <> i And Not (flags(j)) Then
    ' calculate square fo distance
        deltax = inarr(i, 1) - inarr(j, 1)
        deltay = inarr(i, 2) - inarr(j, 2)
        distsq = (deltax * deltax) + (deltay * deltay)
        If distsq < mindis Then
         mindis = distsq
         mindi = j
         
        End If
  End If
 Next j
 Cells(mindi, 4) = cnt
 cnt = cnt + 1
 Cells(mindi, 5) = mindis
 Cells(mindi, 6) = True
 flags(mindi) = True
Next i
End Sub

 

[AmirTaj]

Something is off. I do get the sequence but its all wrong.

https://ibb.co/kiyQQ0

Below is my x and y.
It has 45 points and I already change inarr, i and j range to 46

 0 0
D 8202 -9030 -7425
D 8000 -4470 -14965
D 8002 -11570 -14965
D 8007 -16600 -12415
D 8006 -16600 -14965
D 8008 -16600 -9405
D 8204 -9030 -5425
D 8013 -1790 -12965
D 8001 -1790 -14965
D 8003 -11570 -12415
D 8012 -4470 -12965
D 8009 -13950 -14965
D 8010 -13950 -12415
D 8011 -7390 -12905
D 8200 -11575 -7215
D 8005 -22210 -13205
D 8205 -1545 -6300
D 8004 -22210 -15205
D 8400 -7875 -23150
D 8201 -12325 -7215
D 8203 -7390 -6425
C 8403 -6025 -23050
S 8201 -8330 -7300
S 8000 -3770 -14840
S 8200 -11150 -5300
S 8400 -8550 -24380
S 8204 -4070 -6950
S 8003 -1090 -12840
S 8002 -1090 -14840
S 8001 -3770 -12840
S 8202 -8330 -5300
S 8203 -7940 -6300
S 8205 -1670 -6950
S 8005 -22810 -15080
S 8004 -23620 -14080
S 8006 -22810 -13080
S 8007 -18490 -14680
S 8010 -17150 -9280
S 8008 -17450 -14840
S 8009 -17450 -12290
S 8011 -14500 -14840
S 8012 -14500 -12290
S 8014 -12120 -12290
S 8013 -12120 -14840
VR8401 -5250 -23050
 0 0

Please..help...me...

 

[offthelip]

Something is off. I do get the sequence but its all wrong.

How can I possibly help if you don't tell me what is wrong!
Your list of number means absolutely nothing to me. I believe my code does what you asked for although your requirements are a bit vague, so I am not surprised it isn't what you wanted.
The link you inserted just seemed to come up with a blank page. So I cannot help you further unless you supply some more information