Latitude & Longitude Processing Help Please
Dec 12, 2009
I'm currently working on a project with data that includes columns for both latitude and longitude which I will need to convert from its current form into decimal degrees. The problem is that the coordinates are in the format DDDMMSS followed by a single letter indicating the direction, which means I have to first extract proper degree minute second format before I can do anything. Further complicating the problem is that each of the entries does not necessarily have all of the components I just described (eg some of the latitudes only have degrees and minutes but not seconds) and also if a longitude is less than 100 degrees, the third digit is not used in the data. There are over 2000 records so doing the conversion by hand just won't work and I've only got a few days to present this part of my project anyway.
I'm looking for a formula or series of formulas that will reliably convert something like 1083000W into -108.5000 when the last two zeros may or may not be there and the first digit may also be missing.
View 11 Replies
ADVERTISEMENT
Jul 15, 2014
I currently have an excel file of around 40.000 records with their zip/postal codes accompanying addresses in countries all over Europe, I would like to convert these into latitude and longitude coordinates.
View 2 Replies
View Related
Feb 5, 2009
Some of the data I was given has latitudes and longitudes which are valuable to me but some of the information was entered incorrectly and the lat and long columns have only zeroes for some entries. I used some VBA to sort out all the ones that have zeroes in the lat and long columns because I noticed in a comments block some of them have the latitude and longitude placed in the comments instead of the designated spot.
An example of one of the comment cells is this:
Search and Rescue 30NM S MARTHA'S VINEYARD 40-37.4N 070-43.3W MEDEVAC OF 35YOM E
As such the problem I am having is figuring out how to search through the cells for ones that have a lat and long in them since there is other information and numbers in the comment cell. Keep the cells that have latitudes and longitudes and get rid of the rest. The red highlighted information is what is valuable to me. Another problem is not all of the latitudes are in that format sadly as can be seen by this example cell:
SAR Unreported Vessel/ 42 01. N 067 25. W CLOSED AREA II/041527ZAUG06
If anyone has any insight/references into how I could go about doing this I would pretty much owe you my life since doing it manually would take ages for myself and my group on this project.
View 5 Replies
View Related
Mar 19, 2009
I am using Excel to calculate distances from latitude and longitude GPS positions. These positions have a format as follows:
S36 39.113 E174 25.945
These have been imported into a column, and using VBA I wish to separate latitude and longitude into two other columns. In both cases the minutes will eventually have to be express as decimals of the degrees. I don't mind if numerous columns are used as stages to get the final answer which is 36.234678 in one column and 174.15567 in the other.
View 14 Replies
View Related
May 18, 2011
I have a list of 800 lat/long coordinates. I need to see how many of those 800 are within 3 miles of another list of 7,000 lat/long coordinates.
I have found the following formular to find the distance between 2 lat/longs:
=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1))
*SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *3958.756
Is there a formula that would allow me to run each of the 800 Lat/longs against the list of 7,000 and indicate how many on the original list have a match within 3 miles?
I was thinking a combination of countif with an array formula, but I can get the order of operations correct.
View 9 Replies
View Related
Jul 31, 2008
I'm using excel to try and sort by distance a list of wind farm locations from a location i put in.
This should be relitavely straight forward, however there are a few elements that make it more complicated.
I am getting a list of wind farm locations from the internet, the BWEA site. The latitude and longitude data copied over is in text. I'm using Excel '07 but it still requires me to butcher the numbers out of it with several columns of REPLACE formulas, it isn't elegant and may well be wrong.
The second and harder problem is that I have to first convert the latitude and lonitude values into OSGB grid references. So far i have found the OS maps excel converter, but it only converts one value at a time. I need something that will convert all of them at once (i don't want to go through and individually type in each distance).
After that it is possible to use basic trigonometry to find the distance between them and any grid reference i put in.
View 9 Replies
View Related
Nov 16, 2009
I have a spreadsheet with a column of Longitude values like:
A
172828.383E
I want to somehow split the cell into new individual cells so it will be like this:
A B C
17 28 28.383
View 5 Replies
View Related
Mar 23, 2013
I have list of address with zip code and Latitude and Longitude information. I am trying find a central locations, which will act as hub for rest of the address, by comparing distances from on locations to another.
Basically i need 2 things
1) How to identify the central locations
2) How to groups other locations around the central locations
Criteria here is distance
View 1 Replies
View Related
Feb 25, 2014
I have more than 1000000 coordinates with heights to sort through. The aim is to be able to give a specific radius and check all coordinates within this radius if the slope is more than a maximum slope. If this is the case it need to put the value (in this case) 100 in a new column. The reason for this is we have a reasonable flat terrain but the entire area is filled with Anthills. I need to sort the data. Normal ground points (No Anthills) should be labelled/coded as 200 and anthills as 100. This will allow my program to know the difference between the ground and anthills. In the tab "Input Sheet" I have a small portion of co-ordinates starting from row 8 to row 53 (this will have to extend all the way down to the last row in excel). I need to copy each row starting with row 8 (C8:E8) and paste it in row 2 (C2:E2). Column H indicates if the points are forming an anthill and the code needs to change. I have my final answer in the tab "Final Answer" that I require for my program. Is there any way I can write a VBA code that will check all the point instead of doing it manually.
View 4 Replies
View Related
Feb 2, 2014
I have 2 different sets of data: temperature data recorded at meteo-stations defined by latitude / longitude, and major world cities also defined by latitude / longitude.
After the user chooses the station's coordinates, temperature data is pulled out from the first data set; this works! However, I would like to be able to provide information about the closest city to the chosen meteo-station from the second data set.
File attached with sample data sets.
View 14 Replies
View Related
Jan 28, 2009
In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)
View 3 Replies
View Related
Jul 9, 2014
I'm am trying to extract latitude from a cell that has the longitude and latitude separated by a space followed by a space and addition numbers. Here are three examples:
-89.8498682 35.467965 0
-89.849981 35.468031 82
-89.8500404 35.4681497 107
I tried: =MID(B262,LEN(B262)-LEN(C262),FIND(" ",RIGHT(B262,LEN(B262)-LEN(C262)))) where B2 is the source and C2 is the extracted longitude and it seems to work some of the time, but here is an example of when it doesn't:
-89.8499246 35.468019 0-89.8499246 6 35.46801
View 3 Replies
View Related
Sep 9, 2009
I have two lists of data that are next to each other an example is below:
A B C D E F g
1 Lattitude Longitude Speed Lattitude Longitude Speed
2 52.49 -2.97 30 52.49 -2.97 30
3 52.48 -2.96 30 52.48 -2.97 40
4 52.48 -2.97 30 52.48 -2.96 30
This table goes right down to cells 20099 so it is a huge set of data as you can see i have sorted both sets of data by latitude from highest to lowest.
My problem is that I need something that will go through the data and highlight locations that are identical with different speed limits. So if both the latitude and longitude are the same then compare the speed limit.
So in the above example cells A4 and B4 are the same as cells E3 and F3 and the speed limit is different so I would like this to be highlighted.
However if the Lattitude and longitude is the same and so is the speed limit it does not necessarily need to be highlighted
I can get this to work but only if the lat/longs that are the same is in cells next to each other. I do not know where to start if the identical locations are in separate parts of the spreadsheet.
View 9 Replies
View Related
Aug 10, 2007
Having the Zip Code Tabulation Module from the US Census Bureau, I have the following and much more. But based on the 3 items :
5-digit Zip Code, Longitude, Latitude.
I'm wanting to Select a Zip Code and have (let's say) several Zip Codes plotted (maybe 4 - 10) out from the center point of the map.
either with, or without a connecting straight line .
And possibly a Scalable Miliage indicator |---- 10 Miles ----| which would be based on the distance.
View 9 Replies
View Related
Oct 31, 2007
is there a vba code which I could use such that it will show me how much processing has be completed.
So basically the user first enters all the information required for the calculation(which is basically a bunch of records on which the processing is carried out), then they press a button which will start the calculation however currently the user has to keep on pressing the refresh button (ctrl+alt+F9) and keep on refressing until the results have been calculated then they are available to view.
I was thinking if it is possible that after the user click on the calculate button a form appears which will start the calculation and within the code it will automatically refresh the worksheet ('s) and then when a field within the worksheet (Status) changes to completed the form will then disappear.
View 9 Replies
View Related
Jan 17, 2007
I am trying to do a conditional format for a cell and I think that it will contain some VB.
If a cell(C3) is a certain background color (gold), then I want another cell (E3) to subtract the number within the cell(C3). If the cell(C3) is not gold, then it is not to be subtracted.
View 9 Replies
View Related
Jun 8, 2007
I get equation in the form of a string from the output of a software that I want to modify by Excel. I am no expert in Excel and I am wondering if some expert in Excel at this forum can do this for me.
see the attached Excel file. The string in cell A1 is an example string that I will be explaining how I want this string to be modified. Here are the attributes of the string.
a. The string will have ASCII characters in it.
b. The string is a mathematical equation. I will be pasting it into Mathcad once the Excel program modifies it.
c. The string has parameters and mathematical operators in it (+,-,*,/,^). Each parameter is seperated from another parameter by at least one space. There is no space in a parameter. So, each parameter starts with a space and ends with a space. The parameters of the example string in cell A1 are listed from cells A5 to A14 in the attached file.
d. If a parameter has paranthesis in it (), then the program will disregard any character in the parameter outside the paranthesis and take the characters inside the paranthesis. For example, it will modify the parameter in cell A5 from "F(u)_X1" to "u". If the parameter has no paranthesis in it, like the one in cell A14, then it will take it as is.
e. The format of the string is such that the multiplication sign may be omitted between parameters. If there is no mathematical operator between two adjacent parameters, it should be assumed that the math operator between them is multiplication. So, the Excel program should insert a multiplication sign between them.
f. Then the program will reassemple the string in the form of an equation by appropriately keeping the mathematical operators in the original expression.
The cells B5 to B14 show the modified parameters of the original parameters in the string in cell A1. The Excel program should put together the parameters in B5 to B14 as shown in cell A18.
View 10 Replies
View Related
Dec 7, 2007
I have a cell in my spreadsheet that is used to designate if the row needs to be hidden or not. If the user enter's "Y", the row is hidden. I need to check some of the previous cells for entered data before the row is hidden. If data is not entered in these cells I want the empty cell(s) to be filled with a color. The user will then have to enter a value into all the correct cells before the function to hide the row will work. What is the best way to do this? I am using the following function to check for the "Y" and hide the row.
View 11 Replies
View Related
Jul 25, 2008
I have a large number of sheets with data in the following general format:
x ..... y ............ z etc
A Unique value Unique value
A Unique value Unique value
B Unique value Unique value
B Unique value Unique value
C Unique value Unique value
C Unique value Unique value
D Unique value Unique value
D Unique value Unique value
E Unique value Unique value
E Unique value Unique value
In seperate sheets, I want the following:
x y1 y2
A
B
C
D
E
x z1 z2
A
B
C
D
E
And so on for a number of columns - I always want column "x" with another column, however I want the two values for the same value of x to be next to each other, not above one another.
View 11 Replies
View Related
Feb 10, 2010
Attached is a sample Excel file. In one worksheet, the RawData sheet, there is a column of text data. Each cell in that column has one or more values in the form: 2.x, or 2.x, 2.y, 2.z. The x, y, and z values are "Rqmt Numbers".
In the second worksheet, Counts, I am trying to count the frequency of each Rqmt Number. I've been trying to figure out the VBA code to do this for longer than I'd like to admit.
View 3 Replies
View Related
Aug 27, 2009
Ive found a few ways to speed up the macro in my workbook thats taking about 40 minutes to process usually.
One way - Press Escape and it goes about twice as fast! - Wy does it do this?
Another way - Inserting these lines into the code -
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic
View 9 Replies
View Related
May 12, 2006
I am having trouble coming up with a way to retrieve certain pieces of data out of a text file. It is a file full of commands for some custom software we use. Here is a snippet of the file, the red parts are what I need to capture.
TOTAL RESET
Set 1=-1,8=-1,14=1,51=5,53=2,64=1,67=0,68=1,72=-1;
TABULATE
TA-TOTWWRTHP
TA-TOTCOLES
TA-BI-LO
TA-TOTIND3
TA-TOTIND2 (NORANK)
BY
L-MAT
With ZVARIABLE
FUTS-TOTWWTP
FUTS-TOTCOLS..........................
View 2 Replies
View Related
Oct 10, 2006
I have to apply formulas to many different excel files. They are all seperate, and there are litterally thousands of them. They are all seperated, cause each of them represent and individual user.
I am unsure of how to work on so many files. I cannot select all of them to open at once either, cause they are in different folders.
I need some sort of batch running program, that could go thru each and every one of them, and apply some formulas to them (probably using VBA scripts)
View 4 Replies
View Related
May 11, 2007
I often use multiple workbooks containing large data, (more than 10MB) with formulae built in. When I update one workbook, i cant use the other because Excel is still processing the first. Is there a work around or a MS patch to rectify this, or this is just a limitation of Excel program?
View 9 Replies
View Related
Nov 20, 2009
I've been working on a variety of ways to display a 'loading' animation into Excel while other processes are running and although there seems to be a lot of strategies out there, none seem to jive with the form I've created. I've been reading a lot about "DoEvents", because that seems to be the common theme in trying to display a UserForm while other processes are running. However, I just don't have enough of a grasp of how it works to make it function properly with my form.
Basically, the form I have features several combo boxes, several command buttons, a few text fields and a bunch of labels. On initializing the form, captions for the labels and items in the combo boxes are added based on the ActiveCell and its surrounding values. Additionally, it includes an 'UPDATE' button that changes the captions and values on the form itself without hiding it, and once again makes reference to the active cell. This 'UPDATE' function can take quite some time and this is where I want to have a loading animation running.
Like I said, I've tried to achieve this in a few ways and the most hopeful avenue seems to be creating a separate UserForm with a WebBrowser element that references the animated GIF in question. However, when one UserForm is showing, the code from another will not run until it is closed. Thus, if I call the animation from the UPDATE button, the animation form will appear, but the update itself will not occur until the animation has been manually closed. Which defeats the purpose of a loading animation.
I understand that "DoEvents" is supposed to surrender some of the allotted processor power from the application so other code run, but the documentation surrounding it is rather convoluted to me. I can get it to work for simple operations, but I have not been able to simulate a situation where the command button on one form will show the animation form, run some code and then hide the animation with one click.
View 9 Replies
View Related
Jan 24, 2007
I have created 2 seperate VBA's that I would like to combine and improve upon. One removes Duffs, and the other eliminates blank rows. My goal is to do a copy and paste/special, then remove the duffs, and then delete all rows that do not have data in column H.
I have tried several VBA's listed on this sight, but cannot seem to get a VBA that does everything I need it to do, and do it quickly. I have attached an example of my spreadsheet.
View 5 Replies
View Related
Mar 1, 2007
I want to open each file in a folder, one at a time, and save each file with a new name (based on cells in the worksheet) to a different folder. This is what I have so far:
Sub aaa()
Dim i As Long, files As String, data As String
Dim fName As String
ChDir _
"C:Files1"
Cells.Select
Range("A1").Activate
Selection.ClearContents
Application. ScreenUpdating = False
i = 0
files = Dir("*.txt")
Do While files <> ""
Open files For Input As 1
This code doesn't open any files unless there is a value already in the cells that fName uses.
View 6 Replies
View Related
Mar 8, 2007
I have a VB codes to process the data from a file that located in the folder from directory. This VB will copy the data from the file and paste it into new workbook by clicking the command button. I want to modify this code in order to make VB can process the data from multiple file from multiple folders. Previously, I just write the code for one folder only. So now, I want to modifiy the codes in order to expand more folders. All the results will be pasted into the same workbook.
Private Sub cmdStart_Click()
Application. ScreenUpdating = False
Set outbk = Workbooks.Add
Workbooks.Open "C:" & TextBox1.Value & "" & TextBox2.Value
Sheets("bin_trip").Select
With ActiveSheet
View 9 Replies
View Related
Oct 30, 2008
about making my code a bit more useful.
I started with a basic code as below which basically looks at upto 1000 part ids in column A and goes to my database to find corresponding Product Codes from a table in the database call PART
View 14 Replies
View Related
Dec 30, 2013
I have a very long (> 2 hr) macro that sifts through thousands of files and creates a table of contents (more or less). Anyway, the macro will operate just fine when I am also working at the same PC but if I walk away for an hour, Excel will sometimes generate an error (application defined or object defined error). I believe that it has something to do with the PC going idle although it really isn't going idle because I turn off sleep and hibernate while running the macro. Also, there are only two locations where the error seems to occur. See below:
Code:
Dim oApp As ObjectSet oApp = CreateObject("Shell.Application")
Dim files As New Collection
Dim fZip As Variant
For Each fZip In oApp.Namespace(zipFile).items
View 7 Replies
View Related
