Identify Multiple Locations More Than X Miles Apart
Mar 16, 2014
Distance Calculations Example 1.xlsx
I need quickly identifying multiple locations based on their proximity to an initial location and each subsequent location selected. I have attached an example spreadsheet that is structured in the following manner:
Cell H2 is the 'initial' location that will be used to identify all subsequent locations
Colums A through C are location specific details including latitude and longitude of all 450 possible locations
Column D is a distance calculation based on the difference betwen the location in column A to the 'initial' location in cell H2
Cell F2 is the necessary minimum distance between locations(in miles)
Cell G2 is the number of locations needed(this will vary)
What I am trying to accomplish is to fill column H with the number of locations specified in cell G2 that are atleast the minimum distance in F2 apart from each other, like drawing virtual circles around the locations on a map.
I am pretty sure this can be done with a Macro, but am not sure if that is the best solution or not. I am a novice Excel user, so even though I don't know the solution, I can already see two issues:
1. Whatever the solution is, the reference in column D will need to change to calculate the distance for each subsequent location instead of staying absolute with cell H2(H3 for second location, H4 for 3rd, etc...)
2. With each selection that is made, any location less than the specified distance in cell G2 should be removed from any future choices for subsequent locations.
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
Do we get the option in excel to copy from different locations (just copy a part from say Sheet A, Sheet B and some other excel file) and then pasting all at once in some other file?
I am currently trying to automate some excells workbooks that my company uses. The one i'm working on is a listing of all the change orders we have. Unfortunatly there are roughly a 1000 rows, each containing the information for the change- change #, Date opened, part numbers, change description, status, date closed.
Order 0001 - 10/22/08 - 0156, 7251, 9901 - delete bag - closed - [blank] - Order 0002 - 10/22/08 - 0018, 0612, 0875 - add notes - open - [blank] -
What I want to do is type a part number in, it checks to see if there are any rows that already have that number and have an open status (as apposed to closed) and then tells me where the conflict is. so in the above example if I typed in 0612 it would tell me that number is already in use. I am using excel 2003 if that makes any difference.
I have a file where the links to external spreadsheets need to be updated on a monthly basis. There are a couple of hundred cells containing links along the lines of: =IF(WEEKDAY(F2)=1,E11,IF(WEEKDAY(F2)=7,E11,'J:DAILYDaily ReportUKSeptember 2006[daily_file_AsAt_01Sep2006.xls] Stock - Consolidated'!$G$5))
I want to be able to enter a month and year and have all the links update...i.e if I type in October 2007 I want the links to change to:...............
My wife wanted a excel sheet to track her walking. See Attached. I think I have the time calculations wrong, for example I am entering 1.07 for an hour and 7 minutes.
I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...
I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.
I have a spread sheet in which an account number is located in, lets say, column A. There can be multiple duplicate account numbers here, and thats ok.
However, there is another column, Col. B, which has the name of an action. There are multiple "actions" listed, but the each action can only occur once for each account number. I need to identify if an account number is duplicated and if so, does it have any duplicate values in B. If it does, highlight the duplicates.
The sumproduct essentially checks to see for example that in the A3:AA range whether we have a match .... say a Monday that appears more than once for the client ... say John G. In the example I am using we have John G with three time slots on Monday that he is scheduled thus kkk = 3.
I would like to loop through the ranges in the Schedules sheet and copy those rows where the above statement about John G is true. For the rest of the example we just assume that John only has one scheduled time for each of the rest of the days of the week so in those instances kkk = 1.
I have the following code to loop thru the days of the week:
For j = 3 to 11 WeekDay = Range("AE" & j) ... if kkk > 1 then WeekDay = Range("AE" & (j-cntr)) else WeekDay = Range("AE" & j) Next j
There is a whole bunch of code, including he sumproduct above that then loops thru the Schedules sheet in the appropriate rows and columns copying the instances where John G has a schedule for a particular day of the week.
First I create a schedule sheet for John G and the vba code enters the first row of data ... Monday etc etc in Row A3 thru H3 of this new sheet.
On the second loop through the data on the Schedules sheet it finds another Monday for John G and this is where I would like to hold the WeekDay variable such that it is still pointing to Monday for a total of three loops since kkk = 3 for John G and Monday.
The problem that I am having is that if I hold WeekDay to point at Monday by something like WeekDay = Range("AE" & (j -1)), it seems to work for two instances ... that is the first instance, WeekDay = Range("AE" & j), j here is 3 for example, then the next pass we have WeekDay = ("AE" & j) and here j = 4 but since I reset it back one we are still at 3 ... this unfortunately only works once then I get and empty WeekDay = "". The loop has to loop through the Schedules sheet and I use jj as a variable here which increases by one each pass through ... next j, however ... increases j but then I try to reset it back by one so that it is still pointing to Monday in the Range("AE" & j) section of the Schedules sheet, it is a temporary range.
The following code takes the selected item from a listbox and populates another listbox with the item. The problem is that only the item from column 1 is populated to the other listbox. How can I populate the other listbox with the items from both column 1 and column 2?
Private Sub cmdSelToExc_Click() Dim i As Long With Me.lstProducts ' Copy all selected items, then delete. For i = 0 To .ListCount - 1 If .Selected(i) Then Me.lstExclude.AddItem .List(i) End If Next j Next i For i = .ListCount - 1 To 0 Step -1 If .Selected(i) Then .RemoveItem i End If Next i End With End Sub
I have a set of data about with approx 7500 cells all contained in one column. The data has a series of peaks that happen, and I need to identify each peak value and place it in a cell. The peak does not occur at regular intervals but they are somewhat regular, as in within 130-230 data points. So, if one could find the first peak in the first 200 cells, identify it and store it somewhere, then look in the next 200, store it, etc. I don't know how to do that in excel.
I have a spreadsheet with 20+ columns. Each column contains a list of invoice numbers, eg, 1234. The lists are all of different lengths (see below). I want to find a way to find/highlight/identify those invoice numbers that appear more than once in the entire sheet (eg if invoice 1234 is listed in column B, and also in column H, and/or if invoice 5678 is listed in column A, and also in column J, and also perhaps in column D, etc.)
I've searched this forum but haven't found anything about searching for multiple duplicates in multiple columns. Ideally the matches would be highlighted in different colours, (eg all cells that contain 1234 would be red, those that contain 5678 would be blue, etc., for ease of identification) but I'm not sure if that's possible. I've attached an example that I've shaded myself to show what I mean. I've tried the MODE function but that only tells me which invoice number occurs the most.
Macro - it is currently set up to find a specific text, and activate that cell. From there, the spreadsheet has data in 6 cells in the 6 rows below the active cell. What I need the macro to do is to identify the product based on key words in a range of cells: that is, if any of those cells contain the string "total", than the product type is total, but if "total" is not found, look for keywords such as "alpha" and "beta", to identify the product type. If its not a total product, then i want it to look for "productB", "productc", and so on with relevant keywords until the product is identified.
I think IF(ISNUMBER(SEARCH can be used, but I'm not sure how, and I'm not sure how to search all 6 cells (to make things worse, the number of cells vary, so I'm using the "find" function to figure out the first and last cell that contain data).
I have a file with telemetry data for n individuals. For each individual I have data on a number of days, over the course of a couple of years. On any given day, I have from 1 to several "fixes" for an individual. Fixes range from 1 to 3, with 3 being the "best". Note that I do not necessarily have data for all individuals on a given day. What I want to do is extract the row with best fix for each individual, on each day for which I have data for that individual. If there is more than one best fix for an individual on a given day, then I want to choose the first best fix (temporally). Right now my data is set up with columns:
INDV DATE TIME FIX ....other attributes
INDV = unique code identifying the individual
The data is sorted by INDV, then by DATE, then by TIME.
I figured I could do this using nested IF statements, where "TRUE" would be added to a new column (e.g., HIGHFIX) in the row of the first highest fix on each day for each individual, but have not been successful in doing this. I've also tried conditional formatting. Again, no success here.
I have to manually cut/paste each excel doc which belongs to a specific Store folder, and copy then into their own shared directory, so for example:
Lets say my company has 10 Stores, and I know their online folder locations, the first step I guess would be for me to map the locations of each store, I am not sure what the best way to do this is, would I use the Case select ? to make the set = ?
So for example, Stores 1 - 10.
Set 1 = Dir("Z:Northwest1") Set 2 = Dir("Z:Northwest2") Set 3 = Dir("Z:Northwest3") Set 4 = Dir("Z:Northwest4") Set 5 = Dir("Z:Northwest5") Set 6 = Dir("Z:Northwest6") Set 7 = Dir("Z:Northwest7") Set 8 = Dir("Z:Northwest8") Set 9 = Dir("Z:Northwest9") Set 10 = Dir("Z:Northwest10")
I am not an expert on Case Select, but was thinking:
Select Case myStores Case Is = 1.......................
The code below works. Now I need that while it does the backup file in the same location, we also took into C: Backup_Contabilidade. Finally also put the date and time.
VB: Private Sub Workbook_BeforeClose(Cancel As Boolean) With Application .CommandBars("Cell").Reset End With
I have a spreadsheet on a server, password protected, and certain columns, rows locked...
This is shared with 6 others and can only be accessed one at a time.
If It so that when the user saves the spreadsheet it saves over itself on the server it's located AND on the individual users computer as backup; how would I go about doing this!?
the attached workbook, I am looking for M19, N19 & O19 to read from different locations depending on how many cells are called on when info is entered in P8.
I have many spreadsheets that use shapes such as arrows and other symbols. The process for these spreadsheets is to move the shape (arrow for Ex) to indicate a specific location of pain or something like that.
Once the arrow is in position, the spreadsheet is printed to PDF and and a snapshot (in adobe) is "taken" and the picture (with the arrow) is pasted into our application.
My questions is how can I move the shapes back into position when you want to do it all over again.
When I have a few shapes I use
PHP Code:
Set sShape = ActiveSheet.Shapes("Right Arrow 6") 2 With sShape .Top = 220 .Left = 91 .Rotation = 20 End With
For each shape
But if I have lots of shapes, 50+, it's a bit more cumbersome.
Can I loop through the shapes when I enable the macros to pick up the current positions and use that information when I do a reset (loop back again to move any shape that were touched back to where it was when the spreadsheet was opened?
Currently I have them close the spreadsheet without saving and just reopen.
I made an analysis table that shows a list of prices on a certain company. I have a table on one worksheet and I made a chart (from that table) on another worksheet. I'm using an in-cell drop down list on the table worksheet to change to different companies. Is there a way that I can put the same drop down list on the chart worksheet? Essentially, I want to be able to switch the company on either worksheet and have it change on both worksheets. I'd like to keep the chart and table uniform and running together.
I'm trying to determine the net displacement between GPS locations and am wondering how I can easily code this in Excel. As far as I know, you need to calculate the distance between Point B to Point A, Point C to Point A, Point D to Point A, and so on. The following is the formula that I would use:
A B C
X_Coordinate Y_Coordinate
123456 3700000 Point A
123460 3700010 Point B
Using the following formula (or setup), this is how I would calculate net displacement:
I assume if I want net squared displacement, I would square root this value SQRT(356) = 18.867
If my formula is correct above, I wonder why Excel gives me an answer for the following net displacement formula (same as above just using Excel formula): (A3-A2)^2 + (B3-B2)^2 = 116
Using this same formula, I could incorporate SQRT in as follows: =sqrt(((A3-A2)^2) + ((B3-B2)^2)), which results in 10.77. This value is clearly different than the value above (18.867) thus I wonder if I'm just mis-calculating something in Excel.
I have to select about 200 odd files from a folder, and move them to a different folder. The problem is, I have to manually select the 200 files from a list of approx 10,000 (not in order).
I have a list of the filenames (with extension) in an Excel Spreadsheet, and I'm wondering if there's a way to automatically move the files using an Excel macro or something?
example:
File location = C:EBDumped and I need to move the 200 files to C:EBSent
So I would need to move ONLY the files where the file name is in the Excel list. The file type (extension) is always the same, but the name (and length of the name) is different.
I have 1 list with two columns, where column A is the location (a 3 digit code), and column B is the name (a 4 digit code).
I have a second list with two columns, again column A is the location, and column B is the name.
I want to make sure the locations and names in the second list match what I have in the first list. If they don't match, I somehow want to flag this in a third column.
I am looking to do find the latest date in a list of dates but have several problems: 1) The dates are all in one column on worksheet 1, but are sourced from other worksheets. So even though it looks like '8/7/09', the value of the cell is 'Worksheet2!B4'. This is making the MAX function not work
2) How to deal with the blank cells in the MAX function?
