1

I have a huge data set with x and y coordinates in them. I'm looking to see if two events are close to each other in proximity, and if so, highlight those events or pull them out and put them in a separate worksheet.

X coordinates are in column V
Y coordinates are in column W

I am looking for points that are within a given distance of each other. I want to either highlight or put them in a new worksheet.

One question is how to report this. I might have two points close to each other that are far from two other point that are also close to each other.

My goal is to take a list of thousands of events and pull out the ones that are close to each other on a map without actually having to search through them all. My data set is continuously growing so this would be a huge time saver in the long run.

MatthewD
  • 6,719
  • 5
  • 22
  • 41
Chris
  • 29
  • 7
  • This site is not meant for people to simply request prepackaged code be written for them. Have you attempted this yourself yet? And if you are okay with simply highlighting the offending rows, have you considered just using conditional formatting? – Grade 'Eh' Bacon Aug 19 '15 at 13:58
  • I didn't even know how to begin with this problem. I did a lot of google searching but I hadn't found anyone with an issue like this before. If you have a link to something similar to what I'm looking for, where entries are being compared to other entries within a certain threshold, I'd be happy to begin there and come back with more specific queries later. – Chris Aug 19 '15 at 14:29
  • Do you want to compare points or just latitudes to each other? – MatthewD Aug 19 '15 at 14:53
  • Points. I just thought the easiest way to go about it would be to compare all x coordinates to each other, take the events that had similar x coordinates and out of that group compare all y coordinates to each other, and then copy all events that fit both criteria to a new sheet. I think it's pretty convoluted but I didn't see a better way of doing it. – Chris Aug 19 '15 at 15:03

1 Answers1

1

I'm not sure if you just want to compare x coords. You could find two x coords that are close but the y coords are far apart. Not sure if that is what you want, just things that are close in one aspect. If that is what you want just loop the rows subtracting one from another. If you want to find the distance between two geographic points see below.

You can get the distance between two points using the pythagorean theorem.

Dim OrigPoint As Double
Dim CheckPoint As Double
Dim Distance As Double

OrigPoint = point1X - point2X
CheckPoint = point1Y - point2Y

Distance = Sqr((OrigPoint * OrigPoint) + (CheckPoint * CheckPoint))

If Distance < 300 Then
    'Copy or highlight your data
End If

You will need to loop rows doing something like this. Then using this these in the code above will compare the point on the current row to the point on the next row.

OrigPoint = ActiveWorkbook.Sheets("Sheet1").Range("V" & lrow).Value - ActiveWorkbook.Sheets("Sheet1").Range("V" & lrow + 1).Value

CheckPoint = ActiveWorkbook.Sheets("Sheet1").Range("W" & lrow).Value - ActiveWorkbook.Sheets("Sheet1").Range("W" & lrow + 1).Value
MatthewD
  • 6,719
  • 5
  • 22
  • 41
  • Will this compare all coordinates in the columns to each other? In other words, I'm not picking the coordinate to compare all entries to, I want to see if each coordinate is next to any other coordinate in their respective two columns. And how can I get it to transfer those entries to a new sheet? – Chris Aug 19 '15 at 14:18
  • No, you will have to write a loop that goes through each row. Then load the values into the OrigPoint and CheckPoint. To transfer the data you will have to do a copy paste on the range from one worksheet to the next or something like that. Here are some examples of looping row. http://stackoverflow.com/questions/24377197/iterating-through-populated-rows-in-excel-using-vba And here is how to copy data. http://stackoverflow.com/questions/28038263/copy-data-from-one-excel-sheet-to-another-complex-using-vba-based-on-column-na – MatthewD Aug 19 '15 at 14:32
  • Thank you very much for your advice. I'm very much a beginner at working with VBA and I need all the help I can get. :) – Chris Aug 19 '15 at 14:56