efficient lookup method?

[jonm]

I have a large excel model that has data on 3 tabs - roughly 35 cols x 3K rows for each table and a series of reports on another 90 tabs that reference the table data using vlookup formulas and perform various calculations including manual updates to the values returned from the tables.


The file is approx 10mb in xlsb format and takes about 5min to open - probably due to recalculation. I'd like to reduce both the time it takes to open the file and the size of the file.


What is the most efficient way to structure (currently unsorted export from another system) and reference the data?


I've tested a few options using a subset (1 report and the 3 data tabs) but haven't seen much of a difference; all of the subset files were approx 730k:


1) vlookup

2) index / match

3) intersect(replacing vlookup formulas with named ranges for the tables and intersection of ranges to return values)

4) dget (not scalable as each criteria needs a separate range)

 

[xld]

90 report tabs? did I read that right? How can you ever assimilate that amount of information?

 

[dan_l]

Nop. You might be able to pull a trick with VBA, but you're probably not going to be able to make it a less clunky spreadsheet.


With that kind of volume of data, you may want to consider using access or doing some ninja tricks with the query tool.

 

[kchiba]

You can try to set the calculation mode to manual, this may save some time, but i would go back to basics and see if you need so many reports.


You can also try to reduce the amount of formatting that you are using. But this is usually difficult on reports


cheers


kanti

 

[keymaster]

I ran few simulations earlier for something else and found that the fastest formulas are INDEX+MATCH (array entered, that is one formula in all cells with CSE)

INDEX+MATCH

VLOOKUP


Techniques based on SUMPRODUCT, SUMIF take in-ordniate amount of time on such large datasets.


Also, if possible, sort the data soon after import. You can write a simple macro to automate data refresh + sort (or even setup sort in exporting system...)

 

[Hui]

Jonm


As Chandoo suggested I'd be looking to rationalise your reports, can some of them be combined using smarter queries into one report.


Can any formulas in your Database be automatically applied and the data then converted to values to remove the recalculations.


Have you looked at using Power Pivot, which is much more efficient at handling large data sources of Multi Millions of records than the standard Excel tools

Have a read about it: http://www.powerpivot.com/

 

[keymaster]

Also, if the data is imported once every few days, you can use pivot tables too. They work quite fast, and have no formula recalculation baggage. You can build reports off the pivot values using cell references or getpivotdata.