Plotting Surrounding Zip Codes Based On Longitude & Latitudes
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
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
May 13, 2014
I have about 25 workbooks with 180 formulas in them that I would like changed. The formulas are all in the range I10:Q29 and all have the same basic structure:
[Code]....
I would like to remove "filepath1!cell1*filepath1!cell2-" and "filepath1!cell2-" so that only "(filepath2!cell3*filepath2!cell4)/(filepath2!cell4)" remains.
My VBA is pretty weak, but I imagine it should be possible to use something like the VBA equivalent of len() and mid() to isolate the components of the equation to the left of the - in the numerator and denominator, and remove those components (as well as the -) from the formula.
Attached File : trim formulas.xlsx
View 12 Replies
View Related
Jan 3, 2014
I am trying to find the revenue generated by different groups of accounts to figure out which group is worth the most, and rank the others.Here is a attached screenshot of what I am working with, cell BK22 is the revenue of all of the accounts coded AADA but i need to find an easier way other than plugging it in for all 3500 cells.
View 6 Replies
View Related
Jul 30, 2008
I got some functions from some of you to spot duplicate bar codes
the first function was this =IF(ISERROR(MATCH(D5;$D6:D$65536;0));"";D5)
the second next to it this =COUNTIF($D:$D;$D5)
and the third: =IF(COUNTIF(D:D;D5)>1;"DUPLICATE";"")
well fine but now I dlike to add the numbers of the 2 cells of those duplicate codes. In some cases I have more than 2 identical bar codes.
Example
4905524490831DPFV900B.CEU0649055244908312DUPLICATE
4905524490831DPFV900B.CEU502DUPLICATE
View 9 Replies
View Related
Jul 8, 2009
I am trying to do in the title so here it goes. I want a user to be able (on form 1) to enter a list of codes that refer to specific requirements (e.g. code PB114 may mean the user needs to perform a pressure test) and then excel will automatically display the requirement somewhere below. It is going to be somewhat of a database. In otherwords lets say in cell A1 you type in a code, e.g. PB114. In a range of merged cells or somewhere below that cell I want the information "Perform a pressure test" to appear.
View 5 Replies
View Related
Feb 16, 2009
I have a small query about excel and whilst there is a really simple answer i'm struggling a little bit. I have create a score metric for data surrounding search data.
Basically i need a macro/formula that will take a list of values that may include duplicates and add up the corresponding score and place the results in a table.
See below - the macro will look at a list of values (column b) and add the scores up (column d) and then remove the duplicates and populate the score in (column f/g)
View 9 Replies
View Related
Mar 31, 2014
Is there any vba codes that can show tab/sheet that is based on the filter selection.
For example:
Cell A1 have a data validation of Apple, orange and Mango
I have 3 tabs named Apple, orange and Mango.
So every time I select Apple on the main in page filter. Apple tab will show. and if orange then orange tab will show.
View 3 Replies
View Related
May 30, 2012
I'm currently creating a formula to look up the date based on the alphanumeric codes, my problem is if it crosses the year, say 2011 & 2012, the lookup will be distorted because codes for jan. 1 2011 & jan. 1 2012 is the same. Our alpha numeric code is A01= Jan. 1.
to elaborate further:
wanted to get the ageing days between todays date and item manufactured date:
today: May 30 2012
when the administrator enters the manufacturing date code of "A01" I have a look up of the code that equates A01=Jan 1. which will compute the difference of May 30 2012-Jan. 1 2012 and give me the Age date of 150. Now, if I enter H1, I wanted to have it look up at the 2011 not 2012 of Aug. 1; is there a look up formula that looks up the date already past (current date below), not the future date of under the same database?
View 5 Replies
View Related
Jul 13, 2006
First it should Insert a Column to the left of A. Then it should go to column B and search for the number 99 and delete all rows after it. In column C I want it to do the following formula =vlookup(b2,descriptions!$b$2:$c$250,2,false) and have it copied all the way down to the end of data.
Next I need labels added to the tops of the rows
Next I need the description columns in C to be copied to column I
Finally, beginning at J2, I would like the formula (e2 * g2)/100 entered to the end of data. Attached is sample data.
View 2 Replies
View Related
Mar 5, 2009
I was given a spreadsheet this morning for sorting and am a little lost. I've attached a sample. I'd like to sort the spreadsheet from high to low in terms of gift amount. The problem is that if I sort the Gift Amount column, all of the surrounding information gets sorted as well, messing up the layout. I know I'm missing something here. Is there any way to tell Excel that if it sorts something, sort the surrounding data as it is? Maybe by giving it a range name?
View 4 Replies
View Related
Apr 23, 2008
If I write some VBA that selects a specific cell, for example the highest value in a coumn of data, how do I then copy a given (say 3) cells above and below (and including) this selected cell.
View 6 Replies
View Related
Jan 24, 2014
I am using Excel 2003.I need two distinct sums:
I need to calculate out the loan amount of approved loans by week (weekending on Sunday). For example for the week ending December 28th, 2013
$1,234,567,890 in total loans
I also need to have a list of sums of the loan amounts per code types of approved loans. For the week ending December 28th, 2013
$123,456.00 in total loans for Code: LTV/TLTV > Guideline max
$987,654.00 in total loans for Code: FICO > 10 pts below Guideline min
I need to ignore any loan amounts and codes for any loans that have not yet had a approved date entered and also ignore all loan amounts for any loans that are denied. I have tried doing this in many different ways with Pivot Tables and I am not getting the results I need.
View 1 Replies
View Related
Jul 20, 2014
I have trying for 2 days now to write a macro/VBA that looks up IF the stock codes (i.e., in column A and G) match THEN insert the ID_ISIN from column H in the empty ISIN column C.
or should i use Vlookup?
View 3 Replies
View Related
Jan 5, 2007
how can I create a formulae that
Highlight a Cell IF
Its surrounding cells are
+1 or +2
-1 or -2
away from that cell value.
Attached a file for example.
View 5 Replies
View Related
May 5, 2006
I'm trying to convert various codes from one column of an excel spreadsheet into different codes in another column. I was able to accomplish this with "If" statements, however I'm only able to string together seven of these statements in one command. Is there a better way to add formulas for more than seven conversions? Below is a copy of what I've done so far with the seven converts:
=IF(ISNUMBER(SEARCH("WARN",J2)),"Warning",IF(ISNUMBER(SEARCH("PSSNAP",N2)),"Sales",IF(ISNUMBER(SEARCH("WARN",L2)),"Warning",IF(ISNUMBER(SEARCH("2699",L2)),"Warning",IF(ISNUMBER(SEARCH("4004",L2)),"Warning",IF(ISNUMBER(SEARCH("2036",L2)),"Warning",""))))))
I want to add about 15 more codes to convert within this formula but it's maxed out in the format I'm using.
View 3 Replies
View Related
Aug 1, 2008
I'm trying to create a spreadsheet to update daily, whenever our market intelligence arrives by email. I'm not trying to write a macro which can select a range of cells (G:L) relative to the date in Column B, which represents the value in cell P6. In other words:Read the value of cell P6 Find that value in column B (e.g. B646)highlight the cells in columns G through to L on that row (e.g. select G646:L646) I am then aiming to paste the values in that range of cells, so that those stay in the spreadsheet and are not lost when the next lot of figures comes in the following day.
Range("G646:L646").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=12
Application.CutCopyMode = False
ActiveWorkbook.Save
but that's obviously a static range, rather than looking up the date first.
View 2 Replies
View Related
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
View Related
Dec 2, 2009
I keep track of values in a workbook. I accumulate them on a daily basis (business days) and keep track of the older values.
On the first sheet I have all current values automatically displayed.
All subsequent sheets contain the values for the different locations (>60) by one location per one sheet with multiple entries per location.
Most of the values do not change daily. So I copy the values from the previous day and paste them to the current day’s fields (the row below yesterday's values).
Today’s date (and prior dates as well as subsequent dates) are in column A, the values to be copied are in column B through AZ. With over 60 sheets this job becomes very tedious very quickly...
What I would like to be able to do, with a click of a button, is to go into each sheet (except the first one), go to the current date (in column A), select the field to the right of that date (in column B), go up one field, select both fields (today and last business day) and go from B to AZ (or A to AY in relative terms) copy all those entries, go down one field (to the same row as today’s date) and paste the content. Then repeat that for every following sheet…
As the date field that I am looking for goes down one field with each day I cannot use fixed points to copy and paste from, but have to use the date field as an anchor from whence to find the proper cells.
I do have some values in the following day's fields, that is why I need to copy two rows and not just the values from the previous day...
View 4 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
May 13, 2008
how do I plot the axis data in my graph in an increasing order, when I have my datas in a random order?
In other words, I have bunch of data to be plotted as my x-axis. All numbers are within [0;1], but they all in a random order, like (0.001, 0.923, 0.120, ...). And I want them to be in an increasing order.
View 9 Replies
View Related
Jul 12, 2009
I use spectrometer in my reesearch. The datas are in (a, b) format in single cell. Is it possible to seperate a and b seperately? like a in seperate cell and b in seperate cell?
View 4 Replies
View Related
Aug 6, 2008
i have this set of data (d.jpg). each cell is 2ms.
I want to compute how much is spent on each set (shown as one color) until the sate changes. In other word, the values are voltages, so i want to know how often the voltage toggles. then plot the toggling instances.
View 14 Replies
View Related
