1. ## 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.

2. ## 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. ## 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.

4. ## 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. ## Re: formulas (excel2003)

Hello Hans,
attached is a sample with additional demands.

6. ## Re: formulas (excel2003)

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

NO

8. ## 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. ## 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.

10. ## 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. ## Re: formulas (excel2003)

the shortest,redline.

regards,
dubdub

12. ## 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. ## 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

14. ## 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. ## 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 Last

#### Posting Permissions

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