#1
|
|||
|
|||
trying to find a way to show a closest to using lat and long coordinates
Back again,
Tried a few ways at doing this but need help, using Lat and Long coordinates I would like to find the closest (lets just call for example) "well" I have attached a sheet, the first tab needs to show the closest beside each location. This shows two separate columns, one Lat and the other Long, then using the "well" tab, I would like to find the closest one to each Location. Am sure there must be a way, I just cant get my head around it, have tried doing matches, Chooses, Lookup functions but not having much luck. Any help would be greatly appreciated. Thanks very much Steve |
#2
|
|||
|
|||
Let me get this straight...for each set of coordinates on the first tab, you would like to look up the well on the second tab that is closest?
|
#3
|
|||
|
|||
That is the what I am hoping to achieve, I feel this is impossible so am just having to do this manually on maps. Very time consuming.
|
#4
|
|||
|
|||
Hoo boy...I bet this can be done with code. Perhaps try the Excel programming forum just below this. My coding abilities are rusty as hell, but I bet there's someone there that can steer you right.
|
#5
|
|||
|
|||
So definitely no formulae / calculation. My coding abilities are close to non existent, so I think will just settle with having to use a map app to manually do it. just wanted to make my job at work easier.
I'll try the programming forum they may have some ideas. Thank you for getting back to me. |
#6
|
|||
|
|||
Quote:
I googled "lookup comparing coordinates excel" and there were a lot of articles, some going back as far as 2002. There were a few promising solutions, but they all required code e.g. http://www.excelbanter.com/showthread.php?t=121991 |
#7
|
|||
|
|||
Thanks very much anyway,
|
#8
|
||||
|
||||
Try the following array formula:
Code:
=INDEX(Well!C:C,MATCH(MIN(ACOS(SIN(RADIANS(B7))*SIN(RADIANS(Well!A$2:Well!A$151))+COS(RADIANS(B7))*COS(RADIANS(Well!A$2:Well!A$151))*COS(RADIANS(C7-Well!B$2:Well!B$151)))),ACOS(SIN(RADIANS(B7))*SIN(RADIANS(Well!A$2:Well!A$151))+COS(RADIANS(B7))*COS(RADIANS(Well!A$2:Well!A$151))*COS(RADIANS(C7-Well!B$2:Well!B$151))),0)+1,0) The approximate minimum distance (in km) is given by the array formula: Code:
=MIN(ACOS(SIN(RADIANS(B7))*SIN(RADIANS(Well!A$2:Well!A$151))+COS(RADIANS(B7))*COS(RADIANS(Well!A$2:Well!A$151))*COS(RADIANS(C7-Well!B$2:Well!B$151))))*6367.465
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
Bravo, Paul. Bravo!!!!
|
#10
|
|||
|
|||
Wow! Thanks very much, I don't understand a single part of it, but it works!
Thanks again. Steve |
#11
|
||||
|
||||
FWIW, the standard formula for calculating the distance between two lat/lon coordinates is:
Code:
ACOS(SIN(RADIANS(Lat1))*SIN(RADIANS(Lat2))+COS(RADIANS(Lat1))*COS(RADIANS(Lat2))*COS(RADIANS(Long1-Long2)))*RadiusEarth The MATCH function simply uses two of these formulae (without the '*RadiusEarth' part), first with the MIN function to calculate a minimum spherical value, the second without the MIN function to find which row produces that value. The INDEX function merely outputs the corresponding name from the row returned by the MATCH function in column C.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#12
|
|||
|
|||
Final Excel
Hello,
Can you post the final Excel file with the working functions? I'm having trouble replicating in my file. Thanks, Brian |
#13
|
|||
|
|||
Hi Brian.
Am trying to look for it, but I either did a copy and paste special on the final sheet, or found that in the end might have just used Google Earth. It was a while ago now and i cannot remember for the life of me. Sorry If I do find I will post up on here for you.. Steve |
#14
|
||||
|
||||
See attached.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#15
|
|||
|
|||
Sorry to bump a 3 year old thread, however this gives me exactly what I need however when I try to expand the number of "Wells" being matched from 151 to 766 I return #N/A values. How would edit this code to look up against all 766 wells?
Code:
=INDEX(Well!C:C,MATCH(MIN(ACOS(SIN(RADIANS(B7))*SIN(RADIANS(Well!A$2:Well!A$151))+COS(RADIANS(B7))*COS(RADIANS(Well!A$2:Well!A$151))*COS(RADIANS(C7-Well!B$2:Well!B$151)))),ACOS(SIN(RADIANS(B7))*SIN(RADIANS(Well!A$2:Well!A$151))+COS(RADIANS(B7))*COS(RADIANS(Well!A$2:Well!A$151))*COS(RADIANS(C7-Well!B$2:Well!B$151))),0)+1,0) Regards |
Tags |
find the closest, lat and long |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Show two alternating slides as long as music plays | Magic | PowerPoint | 2 | 11-23-2014 09:02 AM |
Bad view when using Find and Find & Replace - Word places found string on top line | paulkaye | Word | 4 | 12-06-2011 11:05 PM |
Highlight and put bookmark on the closest field to the cursor | b0x4it | Word VBA | 11 | 05-19-2011 11:15 PM |
Question: Hyperlink to other show, but close the current show | habibfikri | PowerPoint | 0 | 12-26-2010 02:37 AM |
PP 2010 .avi file plays during slide show, but causes the slide show to loop to begin | VictorS | PowerPoint | 0 | 10-16-2010 10:23 AM |