Frequent super lag when sort by column of calculations

[default_abuser]

Due to LOCalc ongoing missing feature “sort by color/format” I’ve now got a calculation work around; Thanks to everyone who helped resolve this.

It’s fine, but the worst part is how long it takes to sort by the column that is all calculations.

The calculation I’m using is this: =VLOOKUP(H10,U:V,1,0). On the sheet I’m currently using, the calculation returns a value on 8 lines, returns “#N/A” on 828 lines. So far, so good. To sort by that column and bring all the lines that returned a value to the top, however, takes greater than 60 seconds and lots and lots of CPU churn. If I sort by a column of normal numbers, the sort is instantaneous.

While testing various things to write this question, I found the really long lag only occurs sporadically. Just now I put in a longer list than the 8, and even with a longer list of matches/returned values, the sort was basically instantaneous. But the really long lag is frequent, and occurred more than half the times in my tests.

I’m going to repeat this method about a million times in a million documents almost every work day (unless I relent and purchase good ole’ Excel out of my own pocket), and would love to know what is causing the lag, and a surefire way to avoid it (and safe way to kill the process if I screw up and accidentally initiate this mess?).

Any idea what’s causing it and how I can stop it?

edit: and most times the progress bar shows slow, steady progress and when it’s done, it has done the job correctly–at least. But just now the progress bar finished, and now it’s locked up.

[erAck]

Reordering the VLOOKUP() formula cells and/or data in ranges it looks up leads to dirty (to be recalculated) formula cells, that may happen too often or other change events getting cross, without the document and exact procedure what is sorted how and when there’s not much to say.

[default_abuser]

I feel fairly sure it’s recalculating the cells, but way more than once. What’s most strange is the recalculation takes infinitely more time than the initial calculation (VLOOKUP 10-20 numbers in a list of just over 1,000 lines on this file) is literally instantaneous. That is to say, when I paste the 10-20 numbers that VLOOKUP will compare to the initial set of 1,000 numbers, it finds them instantly–zero churn. And why not–that’s a small computation. Sorting on another column that is plain numbers is instantaneous – and why not, it’s a simply computation. Simply sort a column of very simple calculations, however, and the churn lasts over 1 minute. How does 1 instantaneous computation + 1 more instantaneous calculation = a calculation involving 60 seconds of CPU churn?

I can’t share the document, it’s all private data.

[LeroyG]

Related question: Remove duplicates -- "filter" is hard to understand

[Frants]

Indeed, sorting a calculated field in a large data set is inefficient. This only gives rise to further reflection.

1- In my opinion, the best solution would be to use a database. Large arrays, selections, and sorting are the power of the database.

2- After performing VLOOKUP calculations, you can disable the automatic recalculation mechanism. Choose Data-> Calculate-> AutoCalculate (uncheck). Then sort.

In the future, you can use manual recalculation all the time (Data-> Calculate-> Recalculate). However, this must be remembered in every session of working with a document.

3- Maybe sorting is unnecessary? Maybe a VLOOKUP filter is enough?

[default_abuser]

1 does not help, not even a little bit, which I find surprising. Even with auto-calculate off, the big lag still occurs as big as ever. To drive the point home with an extreme example, when doing an A->Z sort immediately after having just done a Z->A sort, it still lags for more than a minute and I’m literally just asking it to reverse the order of 1,000 lines lol! Auto-calculate on or off, it experiences the same problem.

3, I’ve been existing without the Sort by Color/Format feature all summer as I’ve clumsily adjusted to post-Microsoft LO world. But it feels quite stupid – and is extremely inefficient – to browse through a document several thousand lines long hunting and pecking for 100 or so that need to be deleted. If this were satisfactory, I wouldn’t have come looking for help. If this were satisfactory, I wouldn’t even need VLOOKUP, I could go back to using Conditional Format.

Solution 2, “use a database” is quite a bag of worms. I’ll think about it.

[Frants]

I am at a loss to answer something. I have generated two sheets of test data in the document. There are about 100,000 entries on the “marketing” sheet. There are about 20,000 records on the “transactions” sheet. In fact, there is a lot of latency with automatic recalculation. But with the auto-recalculation option disabled, it is about 5 seconds. We still need to think …