+ Reply to Thread
Results 1 to 5 of 5

Speed up vlookup with Scripting.Dictionary

  1. 11-18-2017, 03:42 AM #1
    dondada82
    dondada82 is offline
    Registered User
    Join Date
    03-23-2017
    Location
    Windsor Locks, CT
    MS-Off Ver
    2013
    Posts
    38

    Speed up vlookup with Scripting.Dictionary

    My current process is the normal vlookup. I tried the double vlookup using true instead of false and it didn't speed up the process. My current process takes 23 (Sheet1 25K+ lines, Sheet2 150+ lines, & Sheet3 6k+ lines). I'm thinking a scripting.dictionary would reduce that time. I'm using 3 work sheets to return data to the first worksheet. Here's my vlookups:

    =IFERROR(VLOOKUP('Sheet1'!C2&"-"&H2,Sheet2!C:P,14,0)," ")
    =IFERROR(VLOOKUP('Sheet1'!C2,CHOOSE({1,2},Sheet3!AI:AI,Sheet3!AF:AF),2,0)," ")
    =IFERROR(VLOOKUP('Sheet1'!C2&"-"&H2,CHOOSE({1,2},Sheet3!AF:AF,Sheet3!AI:AI),2,0)," ")

    Any help will be greatly appreciated. Thanks.
    Reply With QuoteRegister To Reply
  2. 11-18-2017, 11:34 AM #2
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Speed up vlookup with Scripting.Dictionary

    A short Excel sample will help to prepare macro or others stuff
    Can you attached one
    Last edited by PCI; 11-18-2017 at 05:39 PM.
    - Battle without fear gives no glory - Just try
    Reply With QuoteRegister To Reply
  3. 11-18-2017, 12:03 PM #3
    mjr veverka
    mjr veverka is offline
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,151

    Re: Speed up vlookup with Scripting.Dictionary

    You don't need to use the worksheet functions.
    Instead of this try use:
    a. 'Find' method
    b. 'Do ... While/Until ... Loop', 'While ... Wend', 'For ... Each ... Next' constructions
    c. or, if you necessarily want to use them, assign functions to variables, don't do it in worksheet cells
    Reply With QuoteRegister To Reply
  4. 11-18-2017, 12:47 PM #4
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Speed up vlookup with Scripting.Dictionary

    Doing 2 approximate matches should have made a big difference. Please humor me, sort you data by the lookup columns and try these (assumed to be on Sheet1):

    =IF(VLOOKUP(C2&"-"&H2,Sheet2!C:P,14)=C2&"-"&H2,VLOOKUP(C2&"-"&H2,Sheet2!C:P,14)," ")
    =IF(LOOKUP(C2,Sheet3!AI:AI,Sheet3!AF:AF)=C2,LOOKUP(C2,Sheet3!AI:AI,Sheet3!AF:AF)," ")
    =IF(VLOOKUP(C2&"-"&H2,Sheet3!AF:AI,4)=C2&"-"&H2,VLOOKUP(C2&"-"&H2,Sheet3!AF:AI,4)," ")

    If this didn't work, perhaps you could try it with exact (or dynamic named) ranges. Full-column references have you processing a lot of extra rows.

    I just realized formulas 2 and 3 might require different sort-orders. If so, they can't be used together reliably.
    Last edited by leelnich; 11-19-2017 at 07:18 AM.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
    Reply With QuoteRegister To Reply
  5. 11-19-2017, 04:06 PM #5
    scottiex
    scottiex is offline
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,343

    Re: Speed up vlookup with Scripting.Dictionary

    trying to remember how I resolved this last time I had to do a huge vlookup.

    you could fairly easily do a macro that sorted and then calculated it and then sorted the other way and calculated the other vlookup if needed.
    Also you can probably reduce the range each vlookup operates over.

    Might need to understand the nature of the data to do this efficiently (how long are both lists max no of duplicates etc).

    Although I'd be very surprised if the double vlookup really doesn't help.
    If you want something done right... find a forum and ask an online expert.

    Time flies like an arrow. Fruit flies like a banana.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Scripting Dictionary (working as vlookup) and add calculated columns
    By mumsys in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-23-2016, 04:49 AM
  2. [SOLVED] Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Function
    By Doc.AElstein in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 07-12-2016, 08:28 AM
  3. Scripting dictionary help
    By leanne2011 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-04-2016, 09:32 PM
  4. need help with scripting dictionary
    By leanne2011 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 10-04-2014, 04:33 PM
  5. [SOLVED] Using Dictionary in VBA scripting
    By vnzerem in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-04-2014, 02:54 PM
  6. Scripting Dictionary
    By Tendla in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-20-2013, 05:41 AM
  7. [SOLVED] Scripting Dictionary
    By thisisgerald in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 12-18-2012, 01:30 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1