Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Can this formula be made to run faster?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This formula works perfectly, it's just that it runs a little slow. Does anyone have any ideas on how to speed it up?

    =VLOOKUP(A2,$A$1:$A$3000,1,FALSE)&" Found in row "&MATCH(A2,Sheet2!$A$1:Sheet2!$A$3000,0)

    Thanks,
    Noir

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-19 10:50, Noir wrote:
    This formula works perfectly, it's just that it runs a little slow. Does anyone have any ideas on how to speed it up?

    =VLOOKUP(A2,$A$1:$A$3000,1,FALSE)&" Found in row "&MATCH(A2,Sheet2!$A$1:Sheet2!$A$3000,0)

    Thanks,
    Noir
    Change it to:

    =A2&" Found in row "&MATCH(A2,Sheet2!$A$1:$A$3000,0)

    and a question: Is the range in Sheet2 really 3000 rows deep and does it often change?

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, sheets1&2 is 3000 rows deep. I am really using more rows than i need. I normally use closer to 1000 rows but, the other 2000 are there just in case.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Noir, you should also consider "tuning" your 3,000 row list. More common entries should be sorted to the top. For example, if it was a list of salaries... one might expect 'em to be normally distributed so one might place the low and high salaries nearer the end of the list.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-19 11:01, Noir wrote:
    Yes, sheets1&2 is 3000 rows deep. I am really using more rows than i need. I normally use closer to 1000 rows but, the other 2000 are there just in case.
    This really shouldn't matter if you experience a high "hit" rate. Exact matches are quite "expensive" if the hit rate is low.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-19 11:01, Noir wrote:
    Yes, sheets1&2 is 3000 rows deep. I am really using more rows than i need. I normally use closer to 1000 rows but, the other 2000 are there just in case.
    OK. I'll assume column A to be alphanumeric type.

    Activate Insert|Name|Define.
    Enter Drecs as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =MATCH(REPT("z",25),Sheet2!$A:$A)

    Activate Add. (Don't leave yet the Define Name window.)

    Enter Drange as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(Sheet2!$A$1,0,0,Drecs-(ROW(Sheet2!$A$1)-1),1)

    Activate OK.

    Now use:

    =A2&" Found in row "&MATCH(A2,Drange,0)

    Aladin

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for your help Mark!!!

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Aladin!!!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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