Libreoffice 6 calc: speed up vlookup, match, index search [closed]

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

erotavlas gravatar image

updated 2020-09-02 22:26:53 +0200

Alex Kemp 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 reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2021-04-18 12:20:16.994306

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