+ Reply to Thread
Results 1 to 5 of 5

Identify multiple locations more than X miles apart

  1. #1
    Registered User
    Join Date
    10-30-2012
    Location
    Crandall, Texas
    MS-Off Ver
    Excel 2007
    Posts
    15

    Question Identify multiple locations more than X miles apart

    Distance Calculations Example 1.xlsx

    Hello,
    I need help 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 hope this explanation is detailed enough. Please let me know if you have any questions. Thanks in advance.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,101

    Re: Identify multiple locations more than X miles apart

    Hi,

    First let me comment on the #number! error you recieve (sometimes) for distance from "home location" - it has been adressed recently in my other post:
    http://www.excelforum.com/excel-prog...ml#post3622321

    Going to main question:
    I think it will be much easier to do this with macro than with formulas.
    As you rate yourself as novice in Excel, I will show an easy way (probably quick enough but not quickest possible) to acomplish the task with just using a macro to "control" the process. While most of work will be done by the excel formulas and autofilter "engine".

    Settinng the stage:
    1) copy (manually or macro - pretty easy - registered one would do, we will include this in main macro) all location names to empty column - we have column E handy. We will remove names of used or rejected locations from this list.
    2) keep control on number of available locations - in G4 formula (with some overhead):
    Please Login or Register  to view this content.
    3) H2 will act not only as "initial location", but also "current location", so in I2:J2 we will have its coordinates
    Please Login or Register  to view this content.
    and similar in J2
    4) which will allow to change formula in column D2 to refer to current location, not "fixed" H2 (including opening comment):
    Please Login or Register  to view this content.
    and copy down
    5) lets have a look on locations we shall remove from the list by setting autofilter to less or equal F2 (we will later on do this with macro, so registering this step)
    something like
    Please Login or Register  to view this content.
    will be registered. In main macro we will improve this a bit and use to remove "too close" locations from column E
    6) now let's look (may be again registering) on all data which have column E not empty (they are further away than 15 miles and moreover as we will make another steps - were not used and were not closer in previous steps too).
    Please Login or Register  to view this content.
    the first line was registered as afutofilter has been reset
    7) in G6 we calculate minimum distance taking into account only filtered (visible) flocations:
    Please Login or Register  to view this content.
    8) and in H4 we will show the name of this location:
    =INDEX(A:A,MATCH(G6,D:D,0))
    9) some headers in some cells and textbox with comment (used also as button to call macro) see attachment.

    So the stage setup is complete and we can start writing the macro.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,101

    Re: Identify multiple locations more than X miles apart

    Well, that was really long post, but hope that with some teaching in mind not just problem solving
    So few additional comments follow here:

    I wrote the macro simplest way. For instance here:
    Please Login or Register  to view this content.
    would be more elegant and universal to write:
    Please Login or Register  to view this content.
    but probably the first form is easier to read for "newbie".
    So now we have hardcodec 1000 rows (999 locations) limit for the file - change if neeeded


    similar wit formula:
    Please Login or Register  to view this content.
    could be replaced with
    Please Login or Register  to view this content.
    Or removed - I thought about using this later on, but finally not used so it jus gives additional info after macro has finished

    Of course, as one could expect the starting point does matter.
    Try distance 30 miles and starting location 7 and 450 first will lead to respectively 48 or 50 unique locations.

    To deal with such situation it would be wise to include test and stop procedure when there is no more available locations before required number (G2) has been achieved.
    for instance just after loop starts (after For i = .... line in code) you can add:
    Please Login or Register  to view this content.
    If it is OK if there is less than G2 just remove msgbox in the code.

    One can think (Well, some come to my mind by I already spent on it quite some time) of further possible extensions - like trying to find max number of points (depending on starting point) with given distance
    Or looking for a shortes possible sum of min distances (also depending on starting piont etc.)
    Last edited by Kaper; 03-16-2014 at 06:02 AM.

  4. #4
    Registered User
    Join Date
    10-30-2012
    Location
    Crandall, Texas
    MS-Off Ver
    Excel 2007
    Posts
    15

    Thumbs up Re: Identify multiple locations more than X miles apart

    Thanks Kaper, that did exactly what I wanted it to do.

    I did, however, realize that I left one important piece of information out all together. While I do need to eliminate any location within 15 miles of the initial and subsequent locations, instead of selecting the location closest to the selected distance, I also need to take into consideration how many customers are served on the remaining locations and select the location with the highest number of customers as the next location to be measured against. I studied the macro and played with it to make it do almost that, but I am running into another issue now. I added the number of customers per location into column E and adjusted all of the references in the macro to reflect the changes in the columns(I think). I also changed the references in H6 and I4 to reference the MAX value in column E instead of the MIN value in column D. Now the macro runs and selects the next location by highest customer count, but it stops long before the 'MAX Locations' (set to 25 in the example)is reached. I am sure I am missing something simple and I was hoping you could assist again.

    Thank you for the help and the detailed explanations.
    Attached Files Attached Files

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,101

    Re: Identify multiple locations more than X miles apart

    You did the upgrade pretty well. And everything looked fine, so it took me some time to find the reason for this strange behaviour.
    The issue is in step 8 of setup (cell H4). There was only 71 unique numbers of customers so function in H4 easily returned already used one.

    The general formula for H4 shall be (as I understand):
    Please Login or Register  to view this content.
    It is an array formula, so Ctrl+Shift+Enter comitted - not just Enter.

    It looks for the name of nearest (MIN D), not too close to already selected (F not empty) location (A) where Customer number (E) is equal to max of available (H6).

    But it is an array formula related to quite advanced calculations in D, so in this form it slows down the excel substantially.
    Playing wit application.calculation, and calculate when needed does not change it substantially (in code you will see few commented lines which remained after this.

    So I used a restricted subranges of respective columns:
    Please Login or Register  to view this content.
    and then speed is again acceptable.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Need a formula that will calculate miles between multiple destinations
    By gus.tavera in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-09-2017, 07:00 PM
  2. Replies: 5
    Last Post: 08-30-2013, 04:32 PM
  3. calculate TOTAL mileage (first 2 miles set rate) all miles thereafter set rate
    By infinite2006 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-30-2013, 08:26 AM
  4. Copy values from populated cells only, and identify those cell locations.
    By The Machinist in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-19-2012, 07:02 AM
  5. Replies: 1
    Last Post: 02-14-2005, 12:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1