+ Reply to Thread
Results 1 to 7 of 7

User input to get coordinate distance

  1. #1
    Registered User
    Join Date
    11-30-2012
    Location
    Colorado
    MS-Off Ver
    Excel 2003
    Posts
    3

    User input to get coordinate distance

    I have data in the following format in a worksheet:
    X Y name category

    where x and y are integers for coordinates and name and category are are strings.

    I'd like to have a separate sheet where a user will input an x and y coordinate, and select a category, and return the closest 10 points for the selected category. I have the distance formula but don't know the ins and outs of excel to know how to deal with user input. If someone could point me in the direction of features and functions or elaborate on how user input and return results work I think I can figure it out. Thank you for your time.

  2. #2
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: User input to get coordinate distance

    re-post: ignore this... had a brain cramp...
    Last edited by djapigo; 11-30-2012 at 08:32 PM.

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: User input to get coordinate distance

    Insert a few rows above your table. Enter the coordinates of the point of interest, and in a column to the right of the table, calculate the distance of each point to the reference point. Sort the table by the distance column.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Moderator - RIP 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
    29,464

    Re: User input to get coordinate distance

    Hi,

    I think the general approach I'd take is as follows.

    1. Name the two cells for the x & y co-ordinates, say xLoc, yLoc. (It's always good practice to name cells and then use the names in formulae).
    2. Add a fifth helper column to your data. This will contain the distance between each data point and the reference point. This distance can be computed with the normal Pythagoras theorem. e.g for the 1st data point on say row 2
    =SQRT((A2-xLoc)^2+(B2-yLoc)^2)
    3. Now use Data Advanced Filter Copy to another location, to extract from your data the five columns using a criteria range for the category you want.
    4. Once you've extracted all the co-ordinates, sort the output list in ascending order using the distance column as the key for the sort.
    5. The first 10 rows are the closest points.
    Richard Buttrey

    RIP - d. 06/10/2022

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

  5. #5
    Registered User
    Join Date
    11-30-2012
    Location
    Colorado
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: User input to get coordinate distance

    Some of that helped but I still havent been able to get what I'm looking for. I added a link to the spreadsheet to help clarify:

    http://dl.dropbox.com/u/38938412/coorddist.xlsx

    The "closest" sheet has the input and where I want the 10 closest from the other sheets. The sheet to the right has columns where I'd like the ten closest coordinates for the input. The other tabs have the coordinates as well as the distance. What I'm looking to do is have those 10 columns update with the X Y coordinates for the 5 different sheets. I only need to figure out one and then I can get the rest from there but I'm not sure how to go about that. Let me know if I can clarify anything.

  6. #6
    Forum Moderator - RIP 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
    29,464

    Re: User input to get coordinate distance

    Please upload the workbook to this forum. Some of us are reluctant to use external sites.

  7. #7
    Registered User
    Join Date
    11-30-2012
    Location
    Colorado
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: User input to get coordinate distance

    I tried but apparently the file is to big at 19 MB? Is there a way to have the coordinate sheet with the distances automatically sort itself when the input changes on the first sheet? I added images of the two sheets I'm working with. So in D5-D14 of the "Closest" sheet I want the X coordinate of the 10 closest, and E5-14 I want the Y coordinate of the 10 closest. I didn't sort it because the input will change making the closest all different.
    Attached Images Attached Images

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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