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.

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.

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.

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

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.