Page 1 of 1

Calc crashing...

PostPosted: Fri Dec 13, 2019 2:52 pm
by withnail
Hi,

This past year I've been learning to use Calc for small personal projects.
I've pretty much learnt everything I need to know for my own use but now I've run into the problem of there being too much data in the spreadsheet.
I have a sheet with about 20,000 rows and 10 columns of data and when I try to do even just a couple of things like filtering or a simple calculation the program just freezes and/or crashes.
I only use simple functions such as average/count/if/and etc., and also some filtering.
I think the data I'm entering is formatted in some way (copied from a website table).
I could split the data up into 10 parts/sheets to reduce data per sheet and just do 10 seperate calculations but I'd like to be a bit more efficient than that ;-)
So, I was thinking of switching to Base which I've never used....or does anyone have a better suggestion?
I'm trying to avoid the learning curve all over again!

Re: Calc crashing...

PostPosted: Fri Dec 13, 2019 4:08 pm
by FJCC
A 20,000 by 10 sheet should not be a problem. I do not use LibreOffice much and in checking where the user profile is stored I found it has a Safe Mode. I suggest you give that a try. Select the menu Help -> Restart In Safe Mode. Use the default setting of Restore From Backup in the subsequent dialog and Continue in Safe Mode. Is your spreadsheet now stable?

Re: Calc crashing...

PostPosted: Fri Dec 13, 2019 8:01 pm
by withnail
Thanks FJCC.

Ha,I kind of forgot I was on the OpenOffice forum....I switched to Libre because I think I was running Linux a while ago and couldn't install OO on it. Suppose I could switch back because I'm back on Win 10 now.
Thought my pc may have been the problem but I have 8Gb ram and 4.2 ghz quad processor which I assume is ample.

I tried your tip but I don't think there's much difference. I opened the sheet as per your instructions and pasted =countif($A$1:$A$16000;$A1;$B$1:$B$16000;$B1) in a blank column. I then dragged this down about 8000 rows and it took about 5 minutes to complete the task. Also get the "(not responding)" message in the header straight away. If I wait and just let it work through then it will complete but if I then want to perform another task then the time taken roughly doubles....and so on with further tasks.

I've attached the sheet. My version is in Dutch language, not sure if that affects how you see it.... (oh, I tried to attach it but file is apparently too big....823kB?)

Re: Calc crashing...

PostPosted: Fri Dec 13, 2019 8:59 pm
by FJCC
You could post the file on a site like Google docs or dropbox or you can email it to me. Let me know if you want to email it and I will send you a private message with the address.

Re: Calc crashing...

PostPosted: Fri Dec 13, 2019 9:14 pm
by withnail
Great, send me a PM and I'll mail the file, thanks!

Re: Calc crashing...

PostPosted: Sat Dec 14, 2019 8:22 am
by FJCC
I cannot find why the sheet calculates so slowly but I will continue to look at it tomorrow.