Need help with an array...

[grime]

There's gotta be an easier way to do this....

I have a list of location names in column A (about 2500 total
locations)
Column B is that location's latitude
Column C is that location's longitude

I have a complicated formula that figures the distance between
locations taking the latitude and longitude from 2 different locations

my goal is to create a list for each location in column A of all other
locations within 200 miles of that location (based on that complicated
formula).

The file gets too huge, too quickly, if i just create a 2500 x 2500
matrix of distances (not only that, but i have to spread it out over
several tabs, since the limit on columns is only 256)

I know it takes an array formula, but I am totally lost...any help
would be appreciated.

Thanks in advance,

 

[Roger Govier]

One way would be to use VLOOKUP.
Name your range of A1:C2500 as Data
Enter First Location in Cell E1
Enter Second location in cell F1
Carry out your calculation in G1 substituting the latitudes and longitudes
obtained from the the VLOOKUP in your formula
=VLOOKUP(E1,data,2,0) = latitude of Location 1
=VLOOKUP(E1,data,3,0) = longitude of location 1
Similarly substitute F1 to obtain the values for location 2

 

[grime]

I know it uses the VLOOKUP tag, but I'm not sure how to put that into a
array.

Most of the array examples I see use the SUM, IF, AVERAGE tags...

I appreciate the help, but unfortunately, I don't use arrays ofte
enough. Might have to hold my hand through this one..

 

[Bernie Deitrick]

grime,

Even an array formula would be too large.

You could use a sinlge column of formulas. Write the formula to reference the values in the current
row as the first location, and use VLookups to extract the desired Lat and Long of the second
location, which will be listed in a specific cell (let's say $D$1). The formula would determine the
distance from each location to that given location in D1.

Then use macro to cycle all your location names through D1, doing a recalc each time, and filtering
your list to choose the locations where the distance is less than or equal to 200. The macro could
then list those locations (and distances, if desired) to the right of the location, starting in
column E.

Does that seem like a good enough solution?

If so, post back, especially if you need help with the distance formula incorporating VLOOKUP. Post
your formula, and a small segment of you table.

HTH,
Bernie
MS Excel MVP

 

[grime]

Bernie,

No, I don't think that solution works (assuming I am understanding yo
correctly).

Your solution would work for 1 location (listed in D1) for each of th
other 2500 locations, but i need to find distances for all 250
locations to all other 2499 locations. But list only those location
whose distance is 200 or less.

So currently i have a sheet that lists my data like (lat & long is jus
random data in my example):

location lat long
loc1 45 77
loc2 76 -12
loc3 -92 34
loc4 82 33
loc5 104 -52
... ... ...


on my results sheet, i would like the data to appear, where the 1s
column lists all my locations, and the following columns list thos
locations where the distance was less than 200 (according to tha
formula):

loc1 loc4 loc5
loc2 loc3 loc4 loc5
loc3 loc2 loc4
loc4 loc1 loc2 loc3
loc5 loc1 loc2
...

heres that formula btw (its a doozy):
(ACOS((COS((B3/180)*PI())*COS((D3/180)*PI())*COS(((C3-A3)/180)*PI()))+(SIN((B3/180)*PI())*SIN((D3/180)*PI()))))*3963.19
where...
A3=longitude of location 1
B3=latitude of location 1
C3=longitude of location 2
D3=latitude of location 2

thanks again for the help. I hope the problem here isn't because of m
lack of being able to explain this correctly..

 

[Bernie Deitrick]

grime,

Put your table labels in cells A1:C1, Location names in column A starting in
A2 and going to A2500, Latitudes in column B starting in B2 and going to
B2500, and Longitudes in column C, starting in C2 and going to C2500.

Then put this formula in cell D2, and copy down to D2500:

=(ACOS((COS((B2/180)*PI())*COS((VLOOKUP($D$1,$A$1:$C$2500,2,FALSE)/180)*PI())*COS(((VLOOKUP($D$1,$A$1:$C$2500,3,FALSE)-C2)/180)*PI()))+(SIN((B2/180)*PI())*SIN((VLOOKUP($D$1,$A$1:$C$2500,2,FALSE)/180)*PI()))))*3963.19

Then run the macro below.

Note that this will blow up if you have more than 252 locations within 200
miles of a single location.

HTH,
Bernie
MS Excel MVP

Sub NewSub()
Dim myCell As Range
Dim myCopy As Range

On Error GoTo NoCells:

For Each myCell In Range("A2:A25")
Range("D1").Value = myCell.Value
Application.CalculateFull

Range("A125").AutoFilter Field:=4, _
Criteria1:="<=200", Operator:=xlAnd, _
Criteria2:="<>0"
myCopy = Range("A2:A25").SpecialCells(xlCellTypeVisible) _
.SpecialCells(xlCellTypeConstants, 23).Copy
myCell(1, 5).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=True, Transpose:=True
Range("A125").AutoFilter
NoCells:
Resume Next
Next myCell
End Sub

 

[Bernie Deitrick]

Ooops, forgot to modify my test macro fto reflect your ranges:

Sub NewSub()
Dim myCell As Range
Dim myCopy As Range

On Error GoTo NoCells:

For Each myCell In Range("A2:A2500")
Range("D1").Value = myCell.Value
Application.CalculateFull

Range("A12500").AutoFilter Field:=4, _
Criteria1:="<=200", Operator:=xlAnd, _
Criteria2:="<>0"
myCopy = Range("A2:A2500").SpecialCells(xlCellTypeVisible) _
.SpecialCells(xlCellTypeConstants, 23).Copy
myCell(1, 5).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=True, Transpose:=True
Range("A12500").AutoFilter
NoCells:
Resume Next
Next myCell
End Sub

 

[grime]

Bernie,

Yer the man. Thanks.

 

[grime]

Well Bernie,

It seemed to work initially, but something is wrong.

The locations that it is saying are within 200 miles seem to be
correct. I've pulled the lats and longs of a few random locations and
they seem to check out.

The main problem is...

I am getting blank lines, where it is saying there are no locations
within 200 miles, which I know to be incorrect because location 1 (that
has the blank line) is found to be within 200 miles of location 2 (and
location 1 is listed on location 2's line). If 1 is within the radius
of the other, the reverse is true as well. The other strange thing is
the blank lines are occurring always on the same line numbers,
regardless of the order of the locations.

Here are the first 20 lines of data to play around with:

HD6005 -105.487727 44.276084
HD6001 -106.261 42.848
LO1539 -104.80229 41.162875
HD6002 -104.845 41.124
LO1671 -79.97 39.634444
LO567 -80 39.556667
LO627 -77.982521 39.44195
LO473 -81.55298 39.312949
HD4803 -81.55249 39.30966
LO1641 -80.280766 39.27905
LO1805 -80.217824 38.98407
HD8429 -81.953056 38.441667
LO454 -82.262172 38.420536
LO616 -81.829311 38.419323
HD4801 -82.295973 38.389272
HD4802 -81.734462 38.344903
LO675 -81.566168 38.312278
LO1040 -80.839915 38.296803
LO1888 -81.180717 37.801892

thanks again for the help....

 

[Bernie Deitrick]

Grime,

Seems to work fine for me. Here are the values that I get from your data set:

Location
HD6005 HD6005 HD6001 LO1539 HD6002
HD6001 HD6005 LO1539 HD6002
LO1539 HD6005 HD6001 LO1539 HD6002
HD6002 HD6005 HD6001 LO1539
LO1671 LO567 LO627 LO473 HD4803 LO1641 LO1805 HD8429 LO454 LO616 HD4801 HD4802 LO675 LO1040
LO1888
LO567 LO1671 LO567 LO627 LO473 HD4803 LO1641 LO1805 HD8429 LO454 LO616 HD4801 HD4802 LO675
LO1040 LO1888
LO627 LO1671 LO567 LO1641 LO1805 LO1040
LO473 LO1671 LO567 HD4803 LO1641 LO1805 HD8429 LO454 LO616 HD4801 HD4802 LO675 LO1040 LO1888
HD4803 LO1671 LO567 LO473 LO1641 LO1805 HD8429 LO454 LO616 HD4801 HD4802 LO675 LO1040 LO1888
LO1641 LO1671 LO567 LO627 LO473 HD4803 LO1641 LO1805 HD8429 LO454 LO616 HD4801 HD4802 LO675
LO1040 LO1888
LO1805 LO1671 LO567 LO627 LO473 HD4803 LO1641 HD8429 LO454 LO616 HD4801 HD4802 LO675 LO1040
LO1888
HD8429 LO1671 LO567 LO473 HD4803 LO1641 LO1805 LO454 LO616 HD4801 HD4802 LO675 LO1040 LO1888
LO454 LO1671 LO567 LO473 HD4803 LO1641 LO1805 HD8429 LO616 HD4801 HD4802 LO675 LO1040 LO1888
LO616 LO1671 LO567 LO473 HD4803 LO1641 LO1805 HD8429 LO454 HD4801 HD4802 LO675 LO1040 LO1888
HD4801 LO1671 LO567 LO473 HD4803 LO1641 LO1805 HD8429 LO454 LO616 HD4801 HD4802 LO675 LO1040
LO1888
HD4802 LO1671 LO567 LO473 HD4803 LO1641 LO1805 HD8429 LO454 LO616 HD4801 LO675 LO1040 LO1888
LO675 LO1671 LO567 LO473 HD4803 LO1641 LO1805 HD8429 LO454 LO616 HD4801 HD4802 LO1040 LO1888
LO1040 LO1671 LO567 LO627 LO473 HD4803 LO1641 LO1805 HD8429 LO454 LO616 HD4801 HD4802 LO675
LO1040 LO1888
LO1888 LO1671 LO567 LO473 HD4803 LO1641 LO1805 HD8429 LO454 LO616 HD4801 HD4802 LO675 LO1040


Send me your full file and I will take a look. Reply to me, then take out the spaces and change the
dot to . in my email address.

HTH,
Bernie
MS Excel MVP

 

[JoraM7]

Hi will need to bring this topic up again, hope there is help.
I have similiar situation where i have this database in sheet2;

columnA with city names, column B with long and column C with lat

Then i have this data in sheet 1 column A a list of Itinerary such as
this

LAX/DEN/AUS/IAD/FRA/STR//LHR/SIN
LAX/SMF//SFO/DEN/LAX
LAX/SMF//SFO/DEN/LAX
LAX/JFK//EWR/DEN/LAX
SIN/PEN/SIN
SIN/SFO/SIN
SIN/AMS//CDG/SIN
AMS/STR
STR/FRA/EDI/LHR
SIN/PEN/SIN
SIN/LAX/SIN
LAX/DEN/PHL//DEN/LAX
PHL/DEN
SIN/AKL/WLG/AKL/SIN
SIN/HKG//BKK/SIN
HKG/PVG/BKK
SIN/DEL/SIN
SIN/DEL/SIN
SIN/LAX//SFO/HKG/SIN
LAX/SFO
SIN/BOM/SIN
SIN/KUL
KUL/SIN
SIN/PEN/SIN

I need to show in column B the furthest point of destination from the
first point or start point

example;
Column_A_-------------------------Column_B_
SIN/AKL/WLG/AKL/SIN WLG

where SIN as the start point and WLG after the measurement being the
furthest point.

Is this possible?

 

[JoraM7]

Hi will need to bring this topic up again, hope there is help.
I have similiar situation where i have this database in sheet2;

columnA with city names, column B with long and column C with lat

Then i have this data in sheet 1 column A a list of Itinerary such as
this

LAX/DEN/AUS/IAD/FRA/STR//LHR/SIN
LAX/SMF//SFO/DEN/LAX
LAX/SMF//SFO/DEN/LAX
LAX/JFK//EWR/DEN/LAX
SIN/PEN/SIN
SIN/SFO/SIN
SIN/AMS//CDG/SIN
AMS/STR
STR/FRA/EDI/LHR
SIN/PEN/SIN
SIN/LAX/SIN
LAX/DEN/PHL//DEN/LAX
PHL/DEN
SIN/AKL/WLG/AKL/SIN
SIN/HKG//BKK/SIN
HKG/PVG/BKK
SIN/DEL/SIN
SIN/DEL/SIN
SIN/LAX//SFO/HKG/SIN
LAX/SFO
SIN/BOM/SIN
SIN/KUL
KUL/SIN
SIN/PEN/SIN

I need to show in column B the furthest point of destination from the
first point or start point

example;
Column_A_-------------------------Column_B_
SIN/AKL/WLG/AKL/SIN WLG

where SIN as the start point and WLG after the measurement being the
furthest point.

Is this possible?

 

[PY & Associates]

Assuming the furthest point being straight line distance from starting
point,
then
break the location column A into individual cells,
use vlookup to get long and lat,
compute distances of each cell from the first,
pick the maximum distance,
get the corresponding location and put in column B

Yes, it is possible

 

[JoraM7]

Any help on the formula, for im a newbie to this .

 

[JoraM7]

Sample database

ANU 17.13675 -61.792667
SHJ 25.328575 55.51715
AUH 24.432972 54.651139
DXB 25.254997 55.364278
RKT 25.613483 55.938817
HEA 34.210017 62.2283
MZR 36.706914 67.209678
KDH 31.505833 65.847833
JAA 34.400253 70.498853
UND 36.665111 68.910833
KBL 34.565842 69.212419
MMZ 35.930789 64.760917

 

[PY & Associates]

It is a little more than a simple formula, but if you show the steps to any
of your friends, they can code it up for you please.

 

[JoraM7]

Well, if i have a friend who can code it up, I wont be here.

 

[PY & Associates]

I have done one.
How to send to you now please?

 

[JoraM7]

Thanks alot!,
You can send to my email;

(e-mail address removed)

:

 

[Harlan Grove]

Max wrote...

Why not just post a *link* to your sample file
to benefit all newsgroup readers instead ?

Eg: via free filehosts

....

Or better still just use prose (unless you always need thing spoon-fed
in binaries, in which case newsgroups may not be the idea forums). That
way you never get burned by .xls files that contain viruses. Or are you
naive enough not to believe that's an issue?

Anyway, given an initial table of airport codes and corresponding
latitudes and longitudes, say in A1:C12, add 2 extra columns to the
table.

D1:
=RADIANS(B1)

E1:
=RADIANS(C1)

Then create a 2-way table of distances between locations. Since the
radius of the earth wouldn't vary nearly as much as the angles, ignore
it and calculate spherical distances using only the angles. Copy A1:A12
and paste into A16:A27 and paste special transpose into B15:M15. Put
="" in A28 and enter 0 in each cell in B28:M28. Enter the following
formula.

B16:
=IF($A16<>B$15,ACOS(
COS(VLOOKUP($A16,$A$1:$E$12,4,0))*COS(VLOOKUP($A16,$A$1:$E$12,5,0))
*(COS(VLOOKUP(B$15,$A$1:$E$12,4,0))*COS(VLOOKUP(B$15,$A$1:$E$12,5,0))
+SIN(VLOOKUP(B$15,$A$1:$E$12,4,0))*SIN(VLOOKUP(B$15,$A$1:$E$12,5,0)))
+SIN(VLOOKUP($A16,$A$1:$E$12,4,0))*SIN(VLOOKUP($A16,$A$1:$E$12,5,0))),0)

Fill B16 down into B17:B27, then fill B16:B27 right into C16:M27. Then
create the defined name seq referring to

=ROW(INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$A:$A,64))

This is the common setup. All of it could be converted to values. Once
the 2-way table is constructed and converted to values, there'd be no
need to keep the original table.

With the first itinerary record in G1, the following monster array
formula will give the subsequent location furthest from the initial
location.

=INDEX($B$15:$M$15,MATCH(MAX(INDEX($B$16:$M$28,MATCH(LEFT(G1,
FIND("/",G1)-1),$A$16:$A$28,0),0)*($B$15:$M$15=MID(G1&REPT("/",12),
SMALL(IF(MID("/"&G1&REPT("/",11),seq,1)="/",seq),{2;3;4;5;6;7;8;9;10;11;12;13}),
SMALL(IF(MID(G1&REPT("/",12),seq,1)="/",seq),{2;3;4;5;6;7;8;9;10;11;12;13})
-SMALL(IF(MID("/"&G1&REPT("/",11),seq,1)="/",seq),{2;3;4;5;6;7;8;9;10;11;12;13})))),
INDEX($B$16:$M$28,MATCH(LEFT(G1,FIND("/",G1)-1),$A$16:$A$28,0),0),0))