Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I perform a lookup on an 80,000 line array?
I am trying to perform a standard vector LOOKUP on some data I have. The
problem I am facing is that my data array contains over 83 thousand lines and Excel will only work with 65536 lines. What am I supposed to do here? I need the LOOKUP to work to replace city and state based on a zip code look up. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I perform a lookup on an 80,000 line array?
I'm assuming that your data lists contain a zip code, a city name and a state
name in three columns. Let's assume that columns A, B, and C contain the first 65,536 items of data and columns D, E, and F contain the remainder (so you've effectively broken your data array into two separate arrays). Give the first data list the range name "DataList1" and the second data list, "DataList2". Let's say that you'll enter your zip code in cell G1. Your lookup could be the following: City: =IF(ISNA(VLOOKUP(G1,DataList1,2,FALSE)),VLOOKUP(G1 ,DataList2,2,FALSE),VLOOKUP(G1,DataList1,2,FALSE)) State: =IF(ISNA(VLOOKUP(G1,DataList1,3,FALSE)),VLOOKUP(G1 ,DataList2,3,FALSE),VLOOKUP(G1,DataList1,3,FALSE)) "Bartimus" wrote: I am trying to perform a standard vector LOOKUP on some data I have. The problem I am facing is that my data array contains over 83 thousand lines and Excel will only work with 65536 lines. What am I supposed to do here? I need the LOOKUP to work to replace city and state based on a zip code look up. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I perform a lookup on an 80,000 line array?
Alternatively, you could download Excel 2007 beta which overcomes the
65536 row limit - you can have up to 1 million rows. Hope this helps. Pete Paul Mathews wrote: I'm assuming that your data lists contain a zip code, a city name and a state name in three columns. Let's assume that columns A, B, and C contain the first 65,536 items of data and columns D, E, and F contain the remainder (so you've effectively broken your data array into two separate arrays). Give the first data list the range name "DataList1" and the second data list, "DataList2". Let's say that you'll enter your zip code in cell G1. Your lookup could be the following: City: =IF(ISNA(VLOOKUP(G1,DataList1,2,FALSE)),VLOOKUP(G1 ,DataList2,2,FALSE),VLOOKUP(G1,DataList1,2,FALSE)) State: =IF(ISNA(VLOOKUP(G1,DataList1,3,FALSE)),VLOOKUP(G1 ,DataList2,3,FALSE),VLOOKUP(G1,DataList1,3,FALSE)) "Bartimus" wrote: I am trying to perform a standard vector LOOKUP on some data I have. The problem I am facing is that my data array contains over 83 thousand lines and Excel will only work with 65536 lines. What am I supposed to do here? I need the LOOKUP to work to replace city and state based on a zip code look up. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I perform a lookup on an 80,000 line array?
Bartimus wrote:
I am trying to perform a standard vector LOOKUP on some data I have. The problem I am facing is that my data array contains over 83 thousand lines and Excel will only work with 65536 lines. What am I supposed to do here? I need the LOOKUP to work to replace city and state based on a zip code look up. If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook, you might want to fool around with something like Function VlookupMegArray(lookup_value, col_index_num) 'The first 6 lines simply load an 80000x3 array Dim arr(1 To 80000, 1 To 3) For i = 1 To 80000 arr(i, 1) = i arr(i, 2) = i + 80000 arr(i, 3) = i + 160000 Next arr1 = SubArray(arr, 1, 3, 1, 65000) arr2 = SubArray(arr, 1, 3, 65001, 80000) On Error Resume Next VlookupMegArray = _ Application.VLookup(lookup_value, arr1, col_index_num, 0) If IsError(VlookupMegArray) Then VlookupMegArray = _ Application.VLookup(lookup_value, arr2, col_index_num, 0) End If End Function Alan Beban |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I perform a lookup on an 80,000 line array?
Alan Beban wrote...
Bartimus wrote: I am trying to perform a standard vector LOOKUP on some data I have. The problem I am facing is that my data array contains over 83 thousand lines and Excel will only work with 65536 lines. What am I supposed to do here? I need the LOOKUP to work to replace city and state based on a zip code look up. If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook, you might want to fool around with something like Function VlookupMegArray(lookup_value, col_index_num) 'The first 6 lines simply load an 80000x3 array .... The OP's main problem is loading the data. Your udf doesn't address that, but it does beg several questions. First, why leave arr1 and arr2 global variables? It's likely they'd be reused for several lookup calls, so there is reason for them to be persistent, but your udf doesn't address that since it always reloads arr, then arr1 and arr2. Next, why read all 83K (not 80K) records into arr then split arr into two static size arrays rather than read batches of 60K or so records into nested arrays? But that still begs the question why use arrays? A WSH Dictionary object would be a MUCH BETTER idea. In the VBE, add a reference to the Microsoft Scripting Runtime. Then add the following code. '-- begin VBA code ------ Option Explicit 'global, so session-persistent, objects 'REQUIRES reference to Microsoft Scripting Runtime Dim dcc As New Dictionary, dcs As New Dictionary Function adhoclookup(v As String) As String If dcc.Count = 0 Or dcs.Count = 0 Then Call loaddata If dcc.Exists(v) And dcs.Exists(v) Then adhoclookup = dcc.Item(v) & ", " & dcs.Item(v) Else adhoclookup = "" End If End Function Private Sub loaddata() 'this is only a sample routine, reading data from a text file 'modify as needed Const INFILE As String = "d:\foo\bar.txt" Dim fh As Long, s As String, a As Variant fh = FreeFile Open INFILE For Input As fh Do Until EOF(fh) Line Input #fh, s If Not IsEmpty(a) Then Erase a a = Split(s, ",", 3) dcc.Add Key:=a(0), Item:=a(1) dcs.Add Key:=a(0), Item:=a(2) Loop Close fh End Sub '-- end VBA code ------ If you believe you really must use arrays, then use a sensible data structure and input process. For instance, the following code fragments. '-- load process ------ Const RECLMT As Long = 25000 'trade-off between speed and storage Dim i As Long, k As Long, n As Long, t(1 To RECLMT, 1 To 3) As String n = 4 'a should be a reference to a global variant ReDim a(1 To n) k = 0 Do Until EndOfInput 'dummy statement k = k + 1 If k n Then n = n * 2 ReDim Preserve a(1 To n) End If a(k) = t For i = 1 To RECLMT If EndOfInput Then Exit For a(k)(i, 1) = ReadZip 'dummy statement a(k)(i, 2) = ReadCity 'dummy statement a(k)(i, 3) = ReadState 'dummy statement Next i Loop If k < n Then n = k ReDim Preserve a(1 To n) End If '-- lookup procedure ------ Dim k As Long, v As Variant On Error Resume Next For k = LBound(a) To UBound(a) v = Application.WorksheetFunction.VLookup(valuesought, a(k), columnsought, 0) 'no error means got a result If Err.Number = 0 Then Exit For Else Err.Clear Next k 'return v The dummy statements above are placeholders for checking whether the data stream has been exhausted and reading each of the fields from the next record while there's still data. Data should only need to be loaded once per session, and it should be stored in module-level variables in order to achieve persistence. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup, Max, Array | Excel Discussion (Misc queries) | |||
VLOOKUP CONDITION TO RETURN BLANK CELL WHERE LOOKUP VALUE IS NOT IN TABLE ARRAY | Excel Worksheet Functions | |||
i need help with a lookup and/or array type formula | Excel Worksheet Functions | |||
Line chart - date line association gone mad! | Charts and Charting in Excel | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |