I am trying to sort a very large set of data by latitude and longitude to find out which locations are closest together but I have not found an effective way for me to do this.
I am trying to sort a very large set of data by latitude and longitude to find out which locations are closest together but I have not found an effective way for me to do this.
You appear to be storing the lat/long as text rather than numbers.
There are various ways to convert them to numbers, here's one:
- Copy a blank cell.
- Select all the latitude and longitude values.
- Goto Paste>Paste Special... and select Add from the Operation section
If posting code please use code tags, see here.
Right, but whenever I change this, is there a formula that I can use to be able to calculate the distance from a starting point in order to sort all of these?
As the Location ID's would represent points then I would think we could utilize the Pythagorean theorem to find the relative distance between them.
In the attached file I put the following in cell D2 and copied down: =SQRT(SUM((B3-B$2)^2,(C3-C$2) ^2))
Next I selected A2:D6751 and sorted column D from least to greatest (leaving Location 410020 as the starting point)
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks