+ Reply to Thread
Results 1 to 12 of 12

Need assistance to improve speed in looking up value

  1. #1
    Registered User
    Join Date
    08-14-2011
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    59

    Need assistance to improve speed in looking up value

    Hi all,

    I am trying to speed-up my lookup process by employing array. However since I am new in array, I have a lot of difficulties. Normally, I would use application.vlookup to look for a value based on the reference. Now, I am trying to use array..

    I have searched for many topic for this, but I have not found the solution.

    In my attachment, the file contains four sheets, namely rawdata, base, summary and backlog.

    There are two main objectives that I would like to achieve:
    1. In sheet rawdata, I would like to fill column Q with Grouping Name which requires me to do double vlookup (when not working with array). First to look for the material group code which can be found from column X sheet rawdata --> then look for the group name based on the material group code and match it with the description as listed in sheet base.

    So far, my failed code look like this (I am not even reach the objective of returning material group code)
    Please Login or Register  to view this content.
    2. The second question is to get the last usage based on material ID which will be shown in sheet summary:
    The step are as follows:
    a. First I move the data from column P, E and K sheet rawdata to sheet backlog (column A to C)
    b. Sorting the data based on the year, in which the latest year is placed on top
    c. Then I number the appearance based on column A, B and C in sheet backlog. At first I am using application.countif but then jindon has given a much faster way
    HTML Code: 
    d. Based on the numbering, I would like to show the latest year when the material was used in sheet summary. This was made through code below. The idea of the code is to look for the first appearance of number 1 for matching year, material id and plant and to return the year which is basically the latest year in which the material was used.

    My current code, which is long and tends to make the excel crash:
    Please Login or Register  to view this content.
    The data shown in file have been truncated except for sheet backlog. The actual data for sheet rawdata column A are more than 60000 rows and the column S are more than 20000 rows. So I am hoping that someone would shed a light to my problem..

    Your assistance is greatly appreciated
    Attached Files Attached Files
    Last edited by a_driga; 01-26-2015 at 08:57 PM. Reason: adding information & correcting column information

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Need assistance to improve speed in looking up value

    This will get your column Q - I'm unsure of what else to do.

    Please Login or Register  to view this content.
    BTW it's Columns P,E & K
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    08-14-2011
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    59

    Re: Need assistance to improve speed in looking up value

    Quote Originally Posted by xladept View Post
    This will get your column Q - I'm unsure of what else to do.

    Please Login or Register  to view this content.
    BTW it's Columns P,E & K
    Thanks for the response.
    I have tried your code, it works well on the example file. Could you please give me an explanation on the process so that you come-up with that syntax. Some of the command are unfamiliar to me.

    Secondly, when I adopt your code into my original dataset in which the data in column A sheet rawdata has much greater rows that the column S (61191 rows vs 25446 rows respectively) then it gives me error message:

    If I do not change your code, the error message is Run-time error '9': Subscript out of range

    When I change this part of your code:
    Please Login or Register  to view this content.
    then column Q only filled until 25446 without any error message (the same number of rows in column S of the original data)

    The code does not work as intended, maybe to elucidate my problem the improvement that I need is to replace this code so that it can run faster:
    Please Login or Register  to view this content.
    Last edited by a_driga; 01-27-2015 at 02:05 AM. Reason: correcting code result

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Need assistance to improve speed in looking up value

    Try this:

    Please Login or Register  to view this content.
    Last edited by xladept; 01-27-2015 at 04:32 PM. Reason: New variables

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Need assistance to improve speed in looking up value

    Better yet, try this - let me know of issues

    Please Login or Register  to view this content.
    Last edited by xladept; 01-27-2015 at 07:36 PM.

  6. #6
    Registered User
    Join Date
    08-14-2011
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    59

    Re: Need assistance to improve speed in looking up value

    Hi xladept,

    Thanks for your response, however, both of your codes do not give me the result that I expect.

    >> For my first question: your code simply take the material group from column X and then look for the material group description from sheet base column B. Therefore, it does not match with the material ID in column E.
    The condition of my real data as follow:
    1. The material ID is not sorted (because the data is retrieved directly from the database)
    2. The number of row in column A sheet rawdata can be much higher than in column S

    Using the code that I know (vlookup), the step I make are:
    1. Since material ID is not sorted, first I look for the material group using column S to AG as table array. The lookup value is material ID from column A.
    2. Once I get the correct material group, then I do the second lookup with column A to B in sheet base as table array. The lookup value is the material group found using the first lookup
    Following that step, therefore my original code looks like:
    Please Login or Register  to view this content.
    in my actual data, I first doing vlookup for the material group listed in column S (the table array for the vlookup function is column A & B from sheet base) and the result is listed in column AG sheet rawdata.

    However, executing that syntax took a lot of time. Therefore, I am asking in this forum, since I cannot solve it (as you can see in my first post of this thread)

    >> For my second question:
    Objective is to know the time when a material is used (shown by the year of its last usage).
    In order to do so, the method that I have in mind are:
    1. First I prepare a table containing material ID in column A and Plant ID in row 2 sheet summary
    2. I prepare before hand sheet backlog to be filled with data from column E, K and P sheet rawdata
    3. I sort the year in descending order, therefore ensuring that the latest usage will be placed on top for each material ID and its respective plant ID
    4. I numbering that order of appearance, first my own code using countifs method. But then Jindon helped and provided code to make it faster
    5. The reason for doing step 4, is to ensure that number 1 always correlate with the latest usage of a particular material ID and its respective plant
    6. Then, to complete the sheet summary, I use the following code:
    Please Login or Register  to view this content.
    The code function was meant to to look for number 1 (column D) by going through all the data in sheet backlog which has the matching material ID from each row in column A sheet summary and matching plant from each column in row 3 sheet summary. However the above code is too slow and causing excel to crash. Therefore, again, I am trying to seek assistance from this forum.

    I hope that my explanation has provided more detail of what I would like to achieve without using the code that I familiar with (because the process time is very long and tend to crash the excel).
    Last edited by a_driga; 01-27-2015 at 09:29 PM.

  7. #7
    Registered User
    Join Date
    08-14-2011
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    59

    Re: Need assistance to improve speed in looking up value

    xladept,

    I have found the solution from my first question by modify your code a litte:
    Please Login or Register  to view this content.
    Note:
    lra and lrb are the same as your r1 & r2 respectively

    It took me around 6 second to complete the process, if a faster solution existed please assist me.

    As for the second question I am still trying to figure it out..

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Need assistance to improve speed in looking up value

    I guess that I need to see some expected results - did you check out the summary sheet?

  9. #9
    Registered User
    Join Date
    08-14-2011
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    59

    Re: Need assistance to improve speed in looking up value

    Quote Originally Posted by xladept View Post
    I guess that I need to see some expected results - did you check out the summary sheet?
    For the process intended to be used in sheet summary, I also adapt from your given code and it has shown the result that I expected with much faster process time rather than using my previous code. The code that I used are as follows:

    Please Login or Register  to view this content.
    I will consider my thread to be solved, but if you have a better solution I am all ears.

    Thanks xladept for fruitful input.

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Need assistance to improve speed in looking up value

    You're welcome! - and thanks for the rep

    I can't think of how to make it quicker

    Sure, mark this thread solved

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,786

    Re: Need assistance to improve speed in looking up value

    a_driga
    Can you just upload a small sample workbook clearly showing before/after?

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Need assistance to improve speed in looking up value

    @ Jindon - I'd like to see the before and after too

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Improve speed of Sort!
    By stockgoblin42 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-29-2013, 03:12 PM
  2. [SOLVED] Need to improve speed of concatenation macro
    By engineerlady in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-01-2013, 11:19 AM
  3. Improve speed on Trim function
    By TommyN in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 01-12-2013, 09:10 AM
  4. Urgent Help - Improve macro speed
    By Mysore in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-05-2012, 11:38 PM
  5. How to improve web query speed
    By hegisin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-18-2007, 12:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1