Libreoffice 6 calc: speed up vlookup, match, index search

asked 2018-02-25 18:34:06 +0200

erotavlas gravatar image

Hi,

I have installed libreoffice 6.0.1.1 under ubuntu mate 16.04 64 bit. I have a calc file with 35 sheets. In particular, one sheet is made of about 500 rows and about 50 columns.

The sheet has 2 columns with match and index and 1 column with vlookup while the other are simple formulas as countif, count and four operations.

When I add a new row or when I change one value in a cell that requires to update the entire row, the update is extremely slow about 60 seconds. The same when I save the file.

My formulas are:

Nested match, index

=MATCH(1,INDEX(($S$18:$S$463=LARGE($S$18:$S$463,ROWS(B$17:B445)))*(COUNTIF(B$17:B445,$A$18:$A$463)=0),),0)

Nested index, match, index

=INDEX($A$18:$A$463,MATCH(1,INDEX(($AS$18:$AS$463=LARGE($AS$18:$AS$463,ROWS(AW$17:AW445)))*(COUNTIF(AW$17:AW445,$A$18:$A$463)=0),),0))

Standard vlookup

=VLOOKUP(AW446,$A$18:$AS$463,45,0)

I disabled openGL, experimental features and search criteria via:

Menu/Tools/Options/LibreOffice calc/Calculate - Searc criteria = and <> must apply to whole cells.

Unfortunately, I cannot speed the process. I tried also to copy this sheet into a new empty file, but the result is more or less the same. I cannot use openCL since it is not supported by GPU and enabling or disabling it does not make any effect.

What can I do in order to make usable the sheet?

Thank you

edit retag flag offensive close merge delete

Comments

I have i7-3537U CPU with 8 GB DDR3 1600 MHz RAM and EVO 850 SSD.

I followed this guide and now the OS recognizes my openCL v1.2 compatible CPU Intel(R) HD Graphics IvyBridge M GT2. However, libreoffice disable each time openCL from the options.

erotavlas gravatar imageerotavlas ( 2018-02-25 18:49:14 +0200 )edit