Handling Large Data Sets

DanD

Board Regular
Joined
Dec 23, 2008
Messages
124
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top