Vlookup Delay

Vlookup Delay - 消息

#1 已发布: 2018/2/18 12:15:39
NDTM Amarasekera

NDTM Amarasekera

130个赞,共352个帖子。

组: User

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)已下载 1267 次。
1.July WL&RT.xlsx (29.52 KiB)已下载 1093 次。

未找到文件。 未找到文件。
Look within!... The secret is inside you. Best Regards Eng. NDTM Amarasekera - Sri Lanka
#2 已发布: 2018/2/18 15:42:00
Andrey Ivashov

Andrey Ivashov

2,344个赞,共4,057个帖子。

组: Super Administrator

Hello.

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

findrows_1.PNG

vlookup(value,table,column):line(findrows(table,value,column-1),1,1)

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.
#3 已发布: 2018/2/18 16:23:08
Andrey Ivashov

Andrey Ivashov

2,344个赞,共4,057个帖子。

组: Super Administrator

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

WL To Q_mod1.sm (16.4 KiB)已下载 1248 次。
Takes 1.7 sec. on my environment.

Best regards.
#4 已发布: 2018/2/18 17:19:13
Jean Giraud

Jean Giraud

983个赞,共6,866个帖子。

组: User

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)已下载 1291 次。

#5 已发布: 2018/2/18 23:16:33
NDTM Amarasekera

NDTM Amarasekera

130个赞,共352个帖子。

组: User

Wrote

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

  • 有新帖
  • 无新帖