Extremely slow file

[Jay]

I've been doing some ad-hoc analysis at work today and as a quick fix (and
because I don't have the Access query skills) I ended up with 2 sheets:

Sheet 1 - Contains 21,000 rows of data, 10 columns with the first column
being a concatenation of 4 cells from each row

Sheets 2 - Contains 10,000 rows with 7 VLOOKUPs in each row, but each
vlookup is like the following:

=VLOOKUP(A1&$B$1&$C$1&D1,data_range,9,false)

Where the data_range is all 21,000 rows x 10 columnsrange in sheet2

Anyway, the file is simply too unwieldy to use. It takes an age to calculate
the cells, apply autofilter options etc.

So, my question is - how can I make it more useable. I've hard-coded (paste
special-values) most of the rows in sheet1 and applied the lookups about
1000 at a time before hard coding the looked up data and doing the next
batch.

However, I want to change the values in B1 and C1 and run it to capture a
different data set.

Is there any way I can do this without it taking an inordinate length of
time. It kept ending up in a 'Not Responding' state on my work PC.

Any help appreciated.

Jay

 

[Pete_UK]

Have a look at this site for examples of how to speed things up and
alternatives to Vlookup:

http://www.decisionmodels.com/optspeede.htm

Hope this helps.

Pete

 

[Jay]

Thanks Pete,

I'm reviewing the site & trying to implement as many of the suggestions as I
can.

-Jay-

 

[Pete_UK]

OK, thanks for feeding back.

Pete

Thanks Pete,

I'm reviewing the site & trying to implement as many of the suggestions as I
can.

-Jay-