MrRhodes2004
05-20-2013, 10:36 AM
I have data with Latitude and Longitude:
Location, Lat, Long
1,30.6382,-97.5985
2,30.2877,-97.0584
3,30.4178,-97.4824
4,30.3616,-97.3635
5,30.2608,-97.0588
6,30.2955,-97.0056
7,30.4251,-97.5755
I would like to start with location 1 and sort by distance to each successive point. To calculate the distance between the points, I use square root some of the squares from the start point, location 1, to all other points =sqrt(abs(30.6382-30.3877)+abs(-97.5985-(-97.0584))). Then sort locations 2-7 based on SRSS.
I then repeat the process with the new start point and the new location 2, the closest item to 1. Calc SRSS, Sort 3-7. Repeat until all locations have been sorted.
I have attached a workbook that has the information.
VBA Code:
1. Select Full Data Range
2. Select Starting Lat and Long
3. Sort By Distance
4. Put Sorted Data on New Sheet
I am trying to write the code but I can do it with the brute force method – copy, calc, sort, copy calc, sort…. However, I would like suggestions on how to do this via a different method, something more elegant.
Location, Lat, Long
1,30.6382,-97.5985
2,30.2877,-97.0584
3,30.4178,-97.4824
4,30.3616,-97.3635
5,30.2608,-97.0588
6,30.2955,-97.0056
7,30.4251,-97.5755
I would like to start with location 1 and sort by distance to each successive point. To calculate the distance between the points, I use square root some of the squares from the start point, location 1, to all other points =sqrt(abs(30.6382-30.3877)+abs(-97.5985-(-97.0584))). Then sort locations 2-7 based on SRSS.
I then repeat the process with the new start point and the new location 2, the closest item to 1. Calc SRSS, Sort 3-7. Repeat until all locations have been sorted.
I have attached a workbook that has the information.
VBA Code:
1. Select Full Data Range
2. Select Starting Lat and Long
3. Sort By Distance
4. Put Sorted Data on New Sheet
I am trying to write the code but I can do it with the brute force method – copy, calc, sort, copy calc, sort…. However, I would like suggestions on how to do this via a different method, something more elegant.