Excel.. File complexity and size in relation to hardware

413x · 1 Jul 2013 at 14:14

Having issues using my excel worksheet and was wondering what is limiting me

Atm it has several million index match formula which I am considering just using the values generated

I'm up to column xx and row 2000 in excel 2010

Just inserting a row is taking 100 seconds

I'm limited to 32 bit system, but excel is only using 1gb and 1gb is free

Am I limited by mechanical hdd, ram or cpu? It's looking like cpu as one core is working at 100%,

wesimmo · 1 Jul 2013 at 15:38

Not sure what you're bottleneck is, but sometimes your Excel files will just get too big with too many formulas.

One simple way to reduce the impact, especially when you're modifying / adding to such a file, is to set Calculation to manual and then only re-calculate when you want to.

Excel will recalculate every time you do something, such as adding a row, when normally you can probably make all the alterations and then do the calculation once at the end.

edit:

Also consider is Excel the best tool for the job?

I'm guessing that using INDEX and MATCH a lot means you might be able to do the equivalent thing in Access as those functions tend to be used for look up type functions when combined?

413x · 1 Jul 2013 at 16:11

Yes I already have manual calculations on.

I didn't quite realise how large this project was going to be. And have never used access, but yes it is mainly one large growing dataset (grows over time) containing entries wit aassociated codes. These codes require data to be associated with them via index+match that are found in a static pool of codes from an external dataset

Dataset
Code
X
Y
Z
X
Y

Code pool
Code
X-string of data
Y-string of data
Z-string of data

wesimmo · 1 Jul 2013 at 20:13

Might be worth giving Access a try.

It'd be really simple by the sounds of it.

2 tables, one with the list of codes and the relevant text associated with them, the other with your data set.

Then one query to match them up. It depends on what you then do with the data once you've matched it all up though.

Makhaira · 1 Jul 2013 at 20:27

If you're using a lot of VLOOKUP or INDEX&MATCH, then databases are almost certainly going to be a lifesaver for you.

kevpuk · 2 Jul 2013 at 09:23

Another +1 for Access
Excel really isn't that happy when you get to these sort of levels, and Access is designed for exactly the sort of thing you want to do here

wesimmo · 2 Jul 2013 at 09:40

You could probably learn all you need to know to do this sort of query in Access in the time it would take to insert a new row in Excel by the sounds of it!

More than happy to give you a few pointers if you want? Trust me and send me some randomised data if you want with the fields that you have in Excel.

Probably the easiest way to do it would be to import the 2 worksheets with the data and the look ups in, that way Access will build the tables for you.

Then go to the create toolbar, Query Design and add the 2 tables.

Link them on the fields that have the look up values, which is as easy as clicking on one and then dragging it across to the field in the other table.

You can then select the fields that the query will bring back by double clicking them, they'll appear at the bottom of the screen.

The big concern is what you then do with that data, though even that is fairly straight forward to learn to someone who is able to use INDEX and MATCH in Excel like you do.

413x · 2 Jul 2013 at 18:18

I've just found I don't have access at work. Bit of a hassle, access looks good!

Thanks for the above offer! If I get access I'll definitely make use of that!

wesimmo · 2 Jul 2013 at 19:05

They don't have Access? Or they haven't installed it?

I know my place didn't install it as a matter of course as 99% of people would never use it, but did so when i asked.