Multi column memory issue Excel 2013

[Mark Sapey]

Hi,

I have been asked to generate a spread sheet to contain a count of occurrences of text in Excel 2013 32 bit.

The file I am working with is 3637 KB, when I open it it takes > 1.2 GB of memory, which means that I soon get out of memory errors.

There are 7310 different text to search for which means that I have > 7363 columns and about 39000 rows.

To create this I put the text I was searching for in row 2 then used COUNTIF to count for each text. To avoid Excel blowing up, I wrote VBA to fill down column by column, then copied the column to value. I then replaced the 0's with a blank to try to save space in the file.

This file is a sub set of the data that I need to work on. All together I need to work with 13186 columns of data for the 39000 rows.

There are no formulas in the file, everything is copied back to text. There is also no formatting on the cells, or anything that can explain why the memory use is so large.

Excel 2013 is supposed to cope with 16,384 columns and 1,048,576 rows. As there are no formulas in my data, I would expect Excel to be able to handle this comfortably.

I have tried the usual suspects such as removing Excel Add-ins.

Does anyone have any suggestions on how I can get around the memory problem?

Thanks in advance for your suggestions.

Regards,

Mark

 

[Mark Sapey]

Hi,

There are 291,501,170 cells in the range. Of these data is in 5,895,325 cells, which is about 2.02%

I have worked with much larger Excel files, although not using the number of columns required here.

Regards,

Mark

Hi there,

"Excel 2013 is supposed to cope with 16,384 columns and 1,048,576 rows." No, that's not true at all. While it may contain that many cells on the grid, by no means should that give anyone the idea that you can actually fill every cell with data. You have a very, very large spreadsheet. For just values, that file size is astronomical. I'd recommend either a better machine, or putting this in a database and analyzing there, otherwise you'll continue to run into the same problems.

As mentioned, Excel 32-bit will not run as fast as Excel 64-bit. The file will continue taking up quite a bit of memory, because it's a large file, "which means that I have > 7363 columns and about 39000 rows." By my calculation that's over 287 million cells with data, quite large by anyone's estimation. On a typical large Excel file, you'll only see several hundred columns used, not several thousand. Just because the grid is sized the way it is, doesn't mean it's there to fill.