 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2006Need help with an array...
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,  Signaturegrime 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 SignatureRegards Roger Govier > > There's gotta be an easier way to do this.... [quoted text clipped - 19 lines] > > Thanks in advance, 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.. 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 > There's gotta be an easier way to do this.... > [quoted text clipped - 18 lines] > > Thanks in advance, 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.. 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("A1:D25").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("A1:D25").AutoFilter NoCells: Resume Next Next myCell End Sub > Bernie, > [quoted text clipped - 39 lines] > thanks again for the help. I hope the problem here isn't because of my > lack of being able to explain this correctly... 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("A1:D2500").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("A1:D2500").AutoFilter NoCells: Resume Next Next myCell End Sub > Bernie, > [quoted text clipped - 39 lines] > thanks again for the help. I hope the problem here isn't because of my > lack of being able to explain this correctly... Bernie, Yer the man. Thanks. Signaturegrime 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.... Signaturegrime 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 > Well Bernie, > [quoted text clipped - 37 lines] > > thanks again for the help.... 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? SignatureJoraM7 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? SignatureJoraM7 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 > Hi will need to bring this topic up again, hope there is help. > I have similiar situation where i have this database in sheet2; [quoted text clipped - 40 lines] > > Is this possible? Any help on the formula, for im a newbie to this :( . SignatureJoraM7 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 SignatureJoraM7 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. > Any help on the formula, for im a newbie to this :( . Well, if i have a friend who can code it up, I wont be here.:confused: SignatureJoraM7 I have done one. How to send to you now please? > Well, if i have a friend who can code it up, I wont be here.:confused: > I have done one. > How to send to you now please? Why not just post a *link* to your sample file to benefit all newsgroup readers instead ? Eg: via free filehosts http://cjoint.com/index.php http://www.flypicture.com/ -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Max wrote... >>I have done one. >>How to send to you now please? [quoted text clipped - 3 lines] > >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)) >Why not just post a *link* to your sample file >to benefit all newsgroup readers instead ? Let me withdraw the earlier suggestion above (which I had thought was in keeping with the spirit behind the newsgroup to benefit all readers, instead of just one, the OP) It was made with good intents but even that has been probably misunderstood by the earlier responder, PY & Associates The "*" within was merely a highlight that it should just be a link, not an attachment. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Thanks alot!, You can send to my email; joram7@gmail.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.