Handling Large Data Sets

[DanD]

Afternoon All,

I'm working on a modelling tool that has behind it one very large data set.

The data set is sales by product by location.

The tool only reports on one product at a time.

Having all the data and calculations and the data set in the tool makes it 35mb+ and a bit clunky.

One alternative I thought of was to use the on change function to import the relevant data each time the user switches sku's, which shouldnt take too long really..

Any thoughts? tips?

Thanks in advance Dan

 

[jbeaucaire]

Truth is...the size of the dataset doesn't have to reduce the efficiency of your modelling tool, nor require VBA (although VBA is perfectly fine approach to transfering data ondemand).

The real kicker is how you try to massage the data. Attempting to use array formulas would drag a sheet of that size to its knees. But good design in the data itself, including a possible helper column or two, could allow your modelling tool to provide answers in real-time with simple worksheet functions.

Either way, post up a clear sample of data and your modeling tool layout and you will get many ideas, I am sure.