Results 1 to 15 of 18
Thread: formulas (excel2003)

20051205, 10:08 #1
 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

20051205, 10:22 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20051205, 11:30 #3
 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

20051205, 11:36 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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)

20051206, 06:06 #5
 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

20051206, 06:30 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: formulas (excel2003)
Will the number of points per location always be the same?

20051206, 06:55 #7
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 373
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: formulas (excel2003)
NO
TIA
dubdub

20051206, 07:08 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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)

20051207, 10:33 #9
 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 coordinates.TIA
dubdub

20051207, 10:37 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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)?

20051207, 10:40 #11
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 373
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: formulas (excel2003)
the shortest,redline.
regards,
dubdubTIA
dubdub

20051207, 11:26 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20051207, 11:46 #13
 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
dubdubTIA
dubdub

20051207, 12:08 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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

20051208, 08:31 #15
 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