Mark Sapey
New Member
- Joined
- Aug 21, 2014
- Messages
- 6
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
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