Retrieving Data: Speed of beating down rows vs retrieving from array?

[(PeteCresswell)]

Got a "What If" area on a worksheet.

User chooses something called a "Deal" from a validation drop
down.

Then the user chooses one of the deal's "Tranches" from a second
validation drop down.

At this point, we have a unique key composed of
DealName|TrancheNumber.


Now we want to go to a table (whose rows may eventually number in
the low thousands), find a match on that DealName|Tranche number
in column 1 of the table and retrieve 20-30 fields (number of
fields depends on what the user specified in the application
creating the spreadsheet....).

Tom Ogilvy suggested vLookup which I guess is the standard way of
doing something like that.

But I have a couple of reservations:
-----------------------------------------------------------------
1) I can get it to work with fuzzy matches, but not exact matches
(the "range_lookup" parm).

Undoubtedly RCI by Yours Truly, but I don't want to invest the
man hours before I know it's the Good-Right-And-Holy-Path.

2) Seems like with vLookup I'd have 20-30 cells beating down that
data table every time somebody changed DealName|Tranche.
Duplication of effort and all that....

Is vLookup *that* fast? i.e. Is it 20-30 times faster than
VBA code doing the same thing?
-----------------------------------------------------------------

Two variations on an alternative come to mind. The alternative
being to write VBA to make one trip to the table and retrieve
all 20-30 fields.
-----------------------------------------------------------------
1) In WorkSheet_Change() beat down the table via VBA code
and feed the contents of each column to the target worksheet.
One trip to the table instead of 20-30 when Deal|TrancheNumber
changes.

My question on this would be about speed: doing this against
with a few thousand rows kill performance?

I'm guessing that vLookup is heavily optimized - but the
question would be is it heavily enough optimized to make
up for 20-30 invocations as opposed to one trip to the
table with VBA?

2) Assuming that beating down the table with VBA *would*
provoke a performance issue, I could define
an array of Type - something like "TrancheInfo", load
the array once when the workbook is opened, and then beat
down the array instead of the table.

Or are the rows/columns of a worksheet functionally the
same to Excel/VB as an array?

If they're faster, then my question is "what event in the
workbook would be appropriate to load the array in?"

I looked for "Worksheet_Open", but there doesn't seem to
be one and my Excel code window navigation skills are
suspect...

 

[Tom Ogilvy]

I suggested Vlookup to the question you first asked.

if I want multilple cells and I am using code it can use

With Worksheets("Sheet1")
set rng = .Range(.Cells(2,1),.Cells(2,1).End(xldown))
End with

res = Application.Match(Trim(ActiveCell & ActiveCell.Offset(0,1)), rng,0)
if not iserror(res) then
set rng1 = rng(res)
rng1.Offset(1,0).Resize(1,30).copy ActiveCell.offset(0,3)
End if

this eliminates all beating down.
You can also use the find command. This again relies on a composite column.
If you don't want to do that, you can use the find command and find each
instance of the first item and see if the second items match - then pick up
from there

See help on the FindNext command for sample code on finding multiple
instances of a value.

 

[(PeteCresswell)]

Per Tom Ogilvy:

if I want multilple cells and I am using code it can use

With Worksheets("Sheet1")
set rng = .Range(.Cells(2,1),.Cells(2,1).End(xldown))
End with

res = Application.Match(Trim(ActiveCell & ActiveCell.Offset(0,1)), rng,0)
if not iserror(res) then
set rng1 = rng(res)
rng1.Offset(1,0).Resize(1,30).copy ActiveCell.offset(0,3)
End if

this eliminates all beating down.

That's the way I'm going to do it then.

Thanks again.