An automatic electronic device had been installed on the bank of a stream to record and transmit Water Levels (at half hour intervals) to a control center, where the readings were converted to discharge values, for further analysis, using an already established Rating Table. These data are given in an Excel file.

The attached SS file takes nearly 2 minutes to convert 1 month data values (48x31=1488).
I have tried this with several previous SS versions also.
Can this be improved, as we have to deal with data recorded for over 5 years?
My system: Win 10 Pro, 64 bit, Core i5, 6 GB Ram Laptop.
Any help will be much appreciated please

WL To Q.sm (18,46 KiB) scaricato 1246 volta(e).
1.July WL&RT.xlsx (29,52 KiB) scaricato 1088 volta(e).

File non trovato. File non trovato.

Hello.

First impression is that you should try using findrows(...) function (built in into the program) in this case.




Before change:
2 min. 14 sec.

After change:
2.4 sec.

But I see different results of using vlookup implemented in your worksheet (returns 1488 rows vector) and findrows (returns 2976 rows vector). Unfortunately I have no chance to deep into the logic, but this may be a good starting point.

Best regards.

Did find 5 minutes more to look into it and finally I did it:

WL To Q_mod1.sm (16,4 KiB) scaricato 1236 volta(e).
Takes 1.7 sec. on my environment.

Best regards.

Wrote

First impression is that you should try using findrows(...) function (built in into the program) in this case.

Maybe the other way around => vlookup needs be doctored.
Mine works fine 0.06 s scanning 9 rows for one value
FindRows => 2.4 s scanning 31 rows ... for ONE value, is it ?

Utilities Matrix lookup.sm (11,18 KiB) scaricato 1273 volta(e).