  Rank: Advanced Member Groups: Registered
Joined: 05/06/2014(UTC) Posts: 349   Location: Colombo Was thanked: 128 time(s) in 82 post(s)
|
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 (19kb) downloaded 30 time(s). 1.July WL&RT.xlsx (30kb) downloaded 49 time(s).  Edited by user 18 February 2018 18:20:49(UTC)
| Reason: Not specified |
Look within!... The secret is inside you.
Best Regards
Eng. NDTM Amarasekera - Sri Lanka |
|
|
|
|
|
  Rank: Administration Groups: Developers, Registered, Knovel Developers, Administrators, Advanced Member Joined: 11/07/2008(UTC) Posts: 1,636  Was thanked: 2007 time(s) in 677 post(s)
|
Hello. First impression is that you should try using findrows(...) function (built in into the program) in this case.  Code: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.
|
|
|
|
|
|
Rank: Administration Groups: Developers, Registered, Knovel Developers, Administrators, Advanced Member Joined: 11/07/2008(UTC) Posts: 1,636 Was thanked: 2007 time(s) in 677 post(s)
|
Did find 5 minutes more to look into it and finally I did it: WL To Q_mod1.sm (17kb) downloaded 48 time(s).Takes 1.7 sec. on my environment. Best regards.
|
|
|
|
|
|
 Rank: Guest
Groups: Registered
Joined: 04/07/2015(UTC) Posts: 6,866  Was thanked: 983 time(s) in 811 post(s)
|
Originally Posted by: Andrey Ivashov  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 (12kb) downloaded 36 time(s).
|
|
|
|
|
|
Rank: Advanced Member Groups: Registered
Joined: 05/06/2014(UTC) Posts: 349 Location: Colombo Was thanked: 128 time(s) in 82 post(s)
|
Originally Posted by: Andrey Ivashov Did find 5 minutes more to look into it and finally I did it: <a class="attachedImageLink {html:false,image:false,video:false}" href="/forum/resource.ashx?a=22240 Thank you very much Andrey!. It's wonderful. Edited by user 19 February 2018 05:23:16(UTC)
| Reason: Not specified |
Look within!... The secret is inside you.
Best Regards
Eng. NDTM Amarasekera - Sri Lanka |
|
|
|
|
|
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.
