+ Reply to Thread
Results 1 to 33 of 33

Distance between coordinates, shown in a sorted list

  1. #1
    Registered User
    Join Date
    03-16-2016
    Location
    Berlin
    MS-Off Ver
    MS Office 2013
    Posts
    28

    Question Distance between coordinates, shown in a sorted list

    Good morning Excel community,

    I was hoping you guys could help me out with something.

    So I have a list with locations and their coordinates:

    Column A: Location ID
    Column B: Coordinate X
    Column C: Coordinate Y

    Example:

    PG423 648322 246675
    PL423 648321 246674
    PF423 653510 246900
    PT435 653505 246900



    So, what am I actually looking for?
    I want to know the distance between the locations and see what locations are closest to the one I analyse.
    In a perfect world I would have for each location a list showing what other locations are closest (the best would be a list sorted by distance).

    I already found out how to compare two locations and show the distance in meter, however I feel like I am still far away from an actual sorted list. I am not even sure if itís possible without using VBA.
    It would be nice if I put a Location ID in some cell and then it shows me the list with all other locations, sorted by distance and showing the meters.

    I hope I managed to explain myself, if not please let me know.

    I really really appreciate your help.
    Thank you so much in advance!

    Best,
    AFGP

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,206

    Re: Distance between coordinates, shown in a sorted list

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    03-16-2016
    Location
    Berlin
    MS-Off Ver
    MS Office 2013
    Posts
    28

    Re: Distance between coordinates, shown in a sorted list

    Good morning Ali,

    Alright, thanks for all the information.
    I created a dummy excel and attached it. I hope it worked.
    If anything else should be missing please let me know.

    Thanks a lot again!

    Best Regards,
    AFGP
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,206

    Re: Distance between coordinates, shown in a sorted list

    Please explain how you determine the distance.

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,453

    Re: Distance between coordinates, shown in a sorted list

    Since the PL122 co-ordinates are EXACTLY the same as the PG343 co-ords there can be no distance between the two.

    As Ali has said, what's your calculation and how do you get 5 for the the two above?


    If these are normal Cartesian co-ordinates on a flat plane then Pythagoras's formula will return the distance and a Ranking formula would allow you to show their relative order.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  6. #6
    Registered User
    Join Date
    03-16-2016
    Location
    Berlin
    MS-Off Ver
    MS Office 2013
    Posts
    28

    Re: Distance between coordinates, shown in a sorted list

    Hello Richard and Ali,

    My bad, obviously you are right. Those are country specific coordinates and not the typical 'Degrees, minutes, and seconds (DMS)' like for instance in google maps.
    Two locations can have the same coordinates then it would simply be 0 meters, as you said.

    and Yes you are right, those can be treated like Cartesian co-ordinates.
    So I guess when comparing two locations I could maybe use a formula like:
    =ROUND(SQRT(SUMSQ($E$3-E6)+(SUMSQ($F$3-F6))),1)
    However that only works when comparing two sites, right? How could I compare One to Many and then sort them accordingly (like you mentioned).

    Thanks again, especially for being so responsive!

    Best,
    Last edited by AFGP; 07-09-2019 at 05:54 AM.

  7. #7
    Registered User
    Join Date
    03-16-2016
    Location
    Berlin
    MS-Off Ver
    MS Office 2013
    Posts
    28

    Re: Distance between coordinates, shown in a sorted list

    Alright, I now tried to simply use a VLOOKUP to retrieve the coordinates of the Location that I want to compare to all the others and then used my mentioned formula for comparison.

    After that I guess I would just need to sort them by Distance from Smallest to Largest.
    I guess there is no way to make them re-arrange in the right order automatically, or is there?
    If not, no hard feelings about doing two clicks with my mouse.

    I attached you what I have so far. Does that seem right?

    Thanks a lot for your feedback!

    Best,
    Attached Files Attached Files

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,206

    Re: Distance between coordinates, shown in a sorted list

    I'm thinking helper column on the source sheet that uses the variables on the results sheet to generate the distances. Then it would be a case of a formula to produce the sorted list. Or there may well be a PowerQuery solution.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,206

    Re: Distance between coordinates, shown in a sorted list

    Attached is a rough and ready PowerQuery version. Try changing a code using the drop-down in the yellow cell on your results sheet, then click Refresh All on the data ribbon. If this does what you want, I can explain how it's done and help you tidy it up.
    Attached Files Attached Files
    Last edited by AliGW; 07-09-2019 at 07:38 AM.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,206

    Re: Distance between coordinates, shown in a sorted list

    Here's the same file tidied up a bit.
    Attached Files Attached Files
    Last edited by AliGW; 07-10-2019 at 01:56 AM.

  11. #11
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,453

    Re: Distance between coordinates, shown in a sorted list

    A simple macro attached to a button or perhaps a Sheet double click event or some other would perform the sort.

    Please Login or Register  to view this content.
    Obviously if the range D6:G33 is not static the macro would need improving so that it reacted to a dynamically changing range.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,206

    Re: Distance between coordinates, shown in a sorted list

    The sort is already done in the PowerQuery solution, Richard - or are you referring to something else?

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,206

    Re: Distance between coordinates, shown in a sorted list

    I notice that the OP has been back since both of us posted, but hasn't responded.

    AFGP - If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,206

    Re: Distance between coordinates, shown in a sorted list

    For completeness, I thought it might be nice to produce a completely automated version. The attached workbook is macro-enabled (.xlsm). Now, whenever cell D3 is changed, the queries are updated and the results table updates automatically instead of having to manually refresh the query.

    I hope this will be of help to someone.
    Attached Files Attached Files

  15. #15
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Distance between coordinates, shown in a sorted list

    And in a triumphant display of one-upmanship ( ), here's a PowerPivot version, which eliminates the need for a helper column or any VBA.

    We prepare the source data using Power Query, loading two tables to the data model (one table with all the source data, one with a distinct list of Location IDs). Then we can use a single DAX measure to dynamically calculate the distance, based on the filter context in the visualisation.

    Benefits: isolates source data | business logic | presentation stages, and provides (near) instant response.
    Attached Files Attached Files
    Last edited by Olly; 07-10-2019 at 05:08 AM.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,206

    Re: Distance between coordinates, shown in a sorted list

    Thanks, Olly - very edifying! It's slow the first time as it loads the data model, but thereafter it's as instant as dammit.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,206

    Re: Distance between coordinates, shown in a sorted list

    Just wondering how to have a drop-down instead of a slicer. I have tried dragging the Location ID from Locations into Filters, but it's already there. Doesn't seem to do anything.

  18. #18
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Distance between coordinates, shown in a sorted list

    Works for me in the latest file, Ali:

    https://excel.solutions/locationidfilter/

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,206

    Re: Distance between coordinates, shown in a sorted list

    Working now - thanks!

    I'm going to have to learn PowerPivot - the DAX is beyond me at the moment, I am afraid. Good to know about, though.
    Last edited by AliGW; 07-10-2019 at 05:39 AM.

  20. #20
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,453

    Re: Distance between coordinates, shown in a sorted list

    Quote Originally Posted by AliGW View Post
    The sort is already done in the PowerQuery solution, Richard - or are you referring to something else?
    I was referring to AFGP's post #7 where he was wondering whther they could be sorted in the right order.
    He had already created formulae that gave him the results he wanted.

    I offered the simple one line macro as a suggestion which would avoid the need for any other functionality.

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,206

    Re: Distance between coordinates, shown in a sorted list

    Thanks for clarifying, Richard. I thought that was probably it.

  22. #22
    Registered User
    Join Date
    03-16-2016
    Location
    Berlin
    MS-Off Ver
    MS Office 2013
    Posts
    28

    Re: Distance between coordinates, shown in a sorted list

    Good afternoon guys,

    Apologies for my late reply, I was out of the office for the last 2 days, therefore the slow answer.
    Thanks so much for your answers and feedback. It works perfectly, even though I donít understand how you did it. Youíre magicians :D

    However, unfortunately my problem got a little more complicated, however I hope you guys could still help me out.

    I have to big lists with locations and I would like to compare all location 1 to Location 2 and then know for each location what are the 2 closest locations (referring to the data of location 2 list) and its distances.
    I am pretty sure that is very hard to obtain, because we would need to compare many to many.

    I attached a newer excel and tried to map out the problem. I hope it explains the problem, let me know if you need any further details.

    Once again thanks a lot for all the help!

    Best,
    AFGP
    Attached Files Attached Files

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,206

    Re: Distance between coordinates, shown in a sorted list

    I just knew it wasn't going to be that 'simple' ... Here we go again!

    I will have a look.

  24. #24
    Registered User
    Join Date
    03-16-2016
    Location
    Berlin
    MS-Off Ver
    MS Office 2013
    Posts
    28

    Re: Distance between coordinates, shown in a sorted list

    Haha, I guess your experience never fails you :D

    Thanks you so much!
    Please pardon me if my replies take a bit longer, I am currently travelling

  25. #25
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Distance between coordinates, shown in a sorted list

    You could do it entirely with Power Query... Format your source data as two tables, then:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  26. #26
    Registered User
    Join Date
    03-16-2016
    Location
    Berlin
    MS-Off Ver
    MS Office 2013
    Posts
    28

    Re: Distance between coordinates, shown in a sorted list

    Good morning Olly,


    Thanks a lot for your contribution. It really does seem to work
    Unfortunately I have no experience with Power Query at all, so I will spend some time now to understand your 'code'. Or is there any easy way to explain that to me?

    Thanks a lot again!

    Best,
    Adrian

  27. #27
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Distance between coordinates, shown in a sorted list

    Sure, let's step through it. Open the Power Query Editor, and you can click on each step in turn, to see what it's doing.

    Steps T1 and T2 simply get source data from the tables in the workbook.

    #"Crossjoin T2" and #"Expanded T2" crossjoin T1 and T2, so that we have a table with every combination of T1 locations and T2 locations.

    #"Added Distance" adds a column with the calculated distance between the two locations.

    #"Ranked by Partitions" makes a partition (table) for each Loc1, ordered by distance (asc), and adds an Index column, to indicate the distance "rank" of each Loc 2 - so the closest Loc2 to each Loc1 gets "Distance Rank" value 1, the second nearest gets "Distance Rank" value 2, and so on.

    #"Combine Partitions" puts all the partition table back together into one table - but now we have the "Distance Rank" column included.

    #"Filtered Rows" keeps only rows for the nearest two Loc2, for each Loc1.

    #"Removed Columns" - does what it says on the tin, just gets rid of columns we're not interested in seeing in the output.

    #"Merged Columns" - we're going to pivot on Distance Rank (turn each Distance Rank value into a column), so we need a single value to display for each Loc1 / Distance Rank combination. As your final output has TWO values for each of these combinations (Distance and Loc2), we merge them, with an identifiable delimiter, so that we can separate back into two values, later.

    #"Pivoted Columns" does the action I just described - turns each Distance Rank value into a column, with the matching Distance|Loc2 merged value.

    #"Split Column 1" and #"Split Column 2" split our merged Distance and Loc2 values into separate columns.

    Finally, #"Changed Type" changes our data types into the number formats we want to see.


    Hope that's helpful.

  28. #28
    Registered User
    Join Date
    03-16-2016
    Location
    Berlin
    MS-Off Ver
    MS Office 2013
    Posts
    28

    Re: Distance between coordinates, shown in a sorted list

    Hello Olly,

    Thanks so much for the detailed explanation.
    I am currently trying to reproduce it and apply it to my real data.

    Feedback follows.
    Thank youu!

  29. #29
    Registered User
    Join Date
    03-16-2016
    Location
    Berlin
    MS-Off Ver
    MS Office 2013
    Posts
    28

    Re: Distance between coordinates, shown in a sorted list

    Hello again,

    Pardon, I already have a very 'stupid' question.
    In Step 1 when I have to load T1 and T2 from the tables, how do you do that it is in the same query?
    When I select table 1 and create the query 'From Table' and then select table 2 and do the same thing, I end up with 2 separate queries instead of 'another step' withing 1.

    Thanks a lot for your help!

    Best,

  30. #30
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Distance between coordinates, shown in a sorted list

    Use the Advanced Editor to manually adjust code, like in my example.

  31. #31
    Registered User
    Join Date
    03-16-2016
    Location
    Berlin
    MS-Off Ver
    MS Office 2013
    Posts
    28

    Re: Distance between coordinates, shown in a sorted list

    Good morning Olly,

    Yes, that's what I did in the end and it seems to work (almost) perfectly
    A big thanks to you already (and of course the rest of you guys).

    However, there is one little thing I noticed. Sometimes I get the same location for 1st closest and 2nd closest, which shouldn't be the case. Is there any way to avoid that?

    Best,
    AFGP

  32. #32
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Distance between coordinates, shown in a sorted list

    Can you upload some data which demonstrates this happening?

  33. #33
    Registered User
    Join Date
    03-16-2016
    Location
    Berlin
    MS-Off Ver
    MS Office 2013
    Posts
    28

    Re: Distance between coordinates, shown in a sorted list

    Hello Olly,

    I believe I found the mistake, there have been several duplicates in the real data itself. After a clean-up it seems to work perfectly. I will add some more features and give some feedback.

    Thanks so much again for sharing your expertise guys. I am really stunned on how good this forum actually works

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculating distance from Lat/Long GPS Coordinates
    By lukedef90 in forum Excel General
    Replies: 3
    Last Post: 06-19-2017, 01:52 PM
  2. Calculating the distance between coordinates with conditions
    By chucky11 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-22-2017, 09:07 AM
  3. How to calculate distance between coordinates
    By madelingirly in forum Excel General
    Replies: 4
    Last Post: 10-01-2015, 02:00 PM
  4. [SOLVED] geo-coordinates distance issue
    By dmitrij in forum Excel General
    Replies: 1
    Last Post: 02-20-2014, 09:58 AM
  5. Distance between Coordinates
    By dustinh48625 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-11-2013, 09:17 PM
  6. Coordinates and Distance formula
    By cporter5 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-24-2010, 06:41 PM
  7. Distance between two sets of coordinates.
    By Marcus Fox in forum Excel General
    Replies: 2
    Last Post: 03-20-2006, 12:40 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1