Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    formulas (excel2003)

    Say I have an xl data sheet with column A for an area name with x & y coordinates of that area in column B & C. suppose I have more than single point coordinates that I want to find the nearest area name to it from the list within a one kilometer range. The format of findings can take outputting the area name and/or highlighting by color within the data itself.
    TIA
    dubdub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formulas (excel2003)

    If X and Y are in miles (or kilometers or something similar) and if the places aren't too far from each other, you can use a simple formula to calculate the distances. See the attached workbook, it uses MIN, INDEX and MATCH to find the area with the smallest distance. If you enter different coordinates in the cells shaded light blue, you'll see the result change.
    If X and Y are longitude and latitude, you'll have to use very different formulas.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formulas (excel2003)

    Thanks Hans.

    It works fine if the areas and the point to be found is single point coordinates.
    My struggle is that multiple points (coordinates) represent each one of the areas, and the area in question has multiple coordinates.
    TIA
    dubdub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formulas (excel2003)

    Could you post a small example workbook? It's hard to devise something without knowing how your data are laid out.
    (It's ok if the workbook contains fake data)

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formulas (excel2003)

    Hello Hans,
    attached is a sample with additional demands.
    TIA
    dubdub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formulas (excel2003)

    Will the number of points per location always be the same?

  7. #7
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formulas (excel2003)

    NO
    TIA
    dubdub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formulas (excel2003)

    a) Is each area contiguous?
    [img]/forums/images/smilies/cool.gif[/img] If so, do the points represent the vertices of a polygon?
    c) If so, are the corners always listed in the correct order? (I.e. are neighboring points also neighbors in the polygon)

  9. #9
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formulas (excel2003)

    My sincere apology for the late reply,
    I would say that areas are independent, yet CD or b column data represents a sub area name of the area in column a and the X & Y represents the area geographic outline co-ordinates.
    TIA
    dubdub

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formulas (excel2003)

    How do you want to measure the distance between areas? Is it the shortest distance (red line in screenshot) or the distance between the centroids of the areas (blue line in screenshot)?

  11. #11
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formulas (excel2003)

    the shortest,redline.


    regards,
    dubdub
    TIA
    dubdub

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formulas (excel2003)

    I doubt that this can be done with formulas, at least not easily. It could be done with VBA code. I assume there must be GIS (Geographic Information Systems) applications that do this "out of the box", but I have no experience with that.

  13. #13
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formulas (excel2003)

    thank you Hans.

    any suggestions for:
    fill b column with column a data.
    remove or delete dupplicate in column b
    Regards
    dubdub
    TIA
    dubdub

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formulas (excel2003)

    Try this macro:

    Sub FillB()
    Dim n As Long
    For n = 2 To Range("A65536").End(xlUp).Row
    If Range("B" & n) = "" Then
    Range("B" & n) = Range("A" & n)
    End If
    Next n
    For n = Range("B" & 65536).End(xlUp).Row To 2 Step -1
    If Range("B" & n) = Range("B" & (n - 1)) Then
    Range("B" & n).ClearContents
    End If
    Next n
    End Sub

  15. #15
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: formulas (excel2003)

    Hi,

    I had a graphical problem that I solved using the attached spreadsheet, it was about areas & centroids. I DIDN'T write the attached spreadsheet, can't remember where I got it, but it was on one of the many "Excel Tips" pages.

    You might get something from it.

    Jim

Page 1 of 2 12 LastLast

Posting Permissions

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