VBA lookup into large database

Hello everyone,

My problem is this:

I have a large database 10 columns * 40,000 Rows.
One of the titles is a code for identifying the other entry's.
I often have a different spreadsheet, where i draw information out of the database with the vlookup function. These sheets can consist of up to 10,000 of the above mentioned codes.
My problem is that when i write this lookup(vlookup) in VBA code for automation purposes, excel freezes after a minute or so.
I have turned screenupdate and calculation off, although upon exit i turn the latter on.
Is there anyway around this, like arrays, or is excel simply to slow to perform these operations?

Thank you in advance

sort your database by its identifying code and use approximate match vlookup
(True as the last argument).

Even though you have turned off automatic calculation, Excel still
calculates formulae when they are entered.

If you need to do exact match but can sort the data see
http://www.decisionmodels.com/optspe...#Speed_Lookups


Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"erikhs" <[email protected]> wrote in
message news:[email protected]...
>
> Hello everyone,
>
> My problem is this:
>
> I have a large database 10 columns * 40,000 Rows.
> One of the titles is a code for identifying the other entry's.
> I often have a different spreadsheet, where i draw information out of
> the database with the vlookup function. These sheets can consist of up
> to 10,000 of the above mentioned codes.
> My problem is that when i write this lookup(vlookup) in VBA code for
> automation purposes, excel freezes after a minute or so.
> I have turned screenupdate and calculation off, although upon exit i
> turn the latter on.
> Is there anyway around this, like arrays, or is excel simply to slow to
> perform these operations?
>
> Thank you in advance
>
>
> --
> erikhs
> ------------------------------------------------------------------------
> erikhs's Profile:
> http://www.excelforum.com/member.php...o&userid=32788
> View this thread: http://www.excelforum.com/showthread...hreadid=526101
>

Sounds like your requirement is static, so Data=>filter=>Advanced Filter
would be a good way to move a subset of your data to another sheet.

--
Regards,
Tom Ogilvy

"erikhs" wrote:

>
> Hello everyone,
>
> My problem is this:
>
> I have a large database 10 columns * 40,000 Rows.
> One of the titles is a code for identifying the other entry's.
> I often have a different spreadsheet, where i draw information out of
> the database with the vlookup function. These sheets can consist of up
> to 10,000 of the above mentioned codes.
> My problem is that when i write this lookup(vlookup) in VBA code for
> automation purposes, excel freezes after a minute or so.
> I have turned screenupdate and calculation off, although upon exit i
> turn the latter on.
> Is there anyway around this, like arrays, or is excel simply to slow to
> perform these operations?
>
> Thank you in advance
>
>
> --
> erikhs
> ------------------------------------------------------------------------
> erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788
> View this thread: http://www.excelforum.com/showthread...hreadid=526101
>
>

Thank you very much for the quick reply.

I need to make an excact match as the codes can be very similar, and it is important to get the correct information.
The database is frequently updated by others than mysekf, so it is not possible to keep it in the same worksheet.
I will perhaps based on you reply, try to import one of the columns most used into a template of a sort and try to sort, and use avlookup perhaps. Are you aware of any array functions that might do the trick. I must add that the lookup is not generally a problem if performed manually, but in VBA it consumes all available memory.

It sounds like you should be using a database for this, set-processing is
what they are designed for.

MH


"erikhs" <[email protected]> wrote in
message news:[email protected]...
>
> Hello everyone,
>
> My problem is this:
>
> I have a large database 10 columns * 40,000 Rows.
> One of the titles is a code for identifying the other entry's.
> I often have a different spreadsheet, where i draw information out of
> the database with the vlookup function. These sheets can consist of up
> to 10,000 of the above mentioned codes.
> My problem is that when i write this lookup(vlookup) in VBA code for
> automation purposes, excel freezes after a minute or so.
> I have turned screenupdate and calculation off, although upon exit i
> turn the latter on.
> Is there anyway around this, like arrays, or is excel simply to slow to
> perform these operations?
>
> Thank you in advance
>
>
> --
> erikhs
> ------------------------------------------------------------------------
> erikhs's Profile:
> http://www.excelforum.com/member.php...o&userid=32788
> View this thread: http://www.excelforum.com/showthread...hreadid=526101
>

Could you post the chunk of code that does the VLookup?
(I dont see why it should be consuming all available memory, perhaps I have
misunderstood what you are doing).

Did you look at the method on
http://www.decisionmodels.com/optspe...#Speed_Lookups
for doing efficient exact match on sorted data?

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"erikhs" <[email protected]> wrote in
message news:[email protected]...
>
> Thank you very much for the quick reply.
>
> I need to make an excact match as the codes can be very similar, and it
> is important to get the correct information.
> The database is frequently updated by others than mysekf, so it is not
> possible to keep it in the same worksheet.
> I will perhaps based on you reply, try to import one of the columns
> most used into a template of a sort and try to sort, and use avlookup
> perhaps. Are you aware of any array functions that might do the trick.
> I must add that the lookup is not generally a problem if performed
> manually, but in VBA it consumes all available memory.
>
>
> --
> erikhs
> ------------------------------------------------------------------------
> erikhs's Profile:
> http://www.excelforum.com/member.php...o&userid=32788
> View this thread: http://www.excelforum.com/showthread...hreadid=526101
>

Sorry that i have not replied to your suggestions before now.

I will be more specific and post chunck of code.
I receive from the sender, in this case a financial institution, a list of securities each identified by a code, that can either be a known standard or an inhouse code. For this list of securities i compare the codes to the codes i have in the database that are standardized, with the lookup function. The code could be: USD100100200. Sorting the datbase would be unpractical as i need to be able to determine the excactly which securities have been entered when.

Here is the extract:

...Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
....
....
Range("m2", Range("m2").End(xlDown)).Name = "MyRange1"
Range("MyRange1") = "=VLOOKUP(RC[-1],'[-------database.xls]---BONDS'!C2:C5,3,FALSE)"
....
....
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Thank you again for your time

Looks like the database file is not open:
If the database.xls file is not open you will run out of memory because you
will have too many external references (and excel will also be extremely
slow because it will have to repeatedly read the database file): so make
sure you have opened both the xls doing the lookup and the xls (database)
that is being looked up.

When both files are open the time taken to do an exact match lookup is
proportional to the number of rows being scanned before a match is found,
which in your case is large. Approximate match uses binary search which
would be over 1000 times faster on average for 40000 rows.

You dont need to sort the securities list, just the database list. Anyway if
the securities list or the database list is not already time-stamped then
you could add a sequence number before you sort it, if you need it.

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"erikhs" <[email protected]> wrote in
message news:[email protected]...
>
> Sorry that i have not replied to your suggestions before now.
>
> I will be more specific and post chunck of code.
> I receive from the sender, in this case a financial institution, a list
> of securities each identified by a code, that can either be a known
> standard or an inhouse code. For this list of securities i compare the
> codes to the codes i have in the database that are standardized, with
> the lookup function. The code could be: USD100100200. Sorting the
> datbase would be unpractical as i need to be able to determine the
> excactly which securities have been entered when.
>
> Here is the extract:
>
> ..Application.Calculation = xlCalculationManual
> Application.ScreenUpdating = False
> ...
> ...
> Range("m2", Range("m2").End(xlDown)).Name = "MyRange1"
> Range("MyRange1") =
> "=VLOOKUP(RC[-1],'[-------database.xls]---BONDS'!C2:C5,3,FALSE)"
> ...
> ...
> Application.ScreenUpdating = True
> Application.Calculation = xlCalculationAutomatic
>
> Thank you again for your time
>
>
> --
> erikhs
> ------------------------------------------------------------------------
> erikhs's Profile:
> http://www.excelforum.com/member.php...o&userid=32788
> View this thread: http://www.excelforum.com/showthread...hreadid=526101
>

You should be able to query the database to match a group of in-house codes to equivalent standard codes. (I'm assuming you need to do that as a first step because the rest of the data is organized according to the standard codes.) Running a database query from Excel is relatively easy. You can work with the results directly in a recordset or dump onto a sheet.

Bill