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.

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.)

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.