+ Reply to Thread
Results 1 to 3 of 3

Help speeding up matching a list of values over multiple workbooks

  1. #1
    Registered User
    Join Date
    01-10-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    15

    Help speeding up matching a list of values over multiple workbooks

    So I have a matrix of data and one of the columns has a bunch of ID's. I'm trying to make a script that can load N workbooks and see if any of those ID's appear in the other workbooks, and build a new list of those ID's and which workbooks they appear in.

    To do this I have a Dictionary where each element is a matrix of the data in the N workbooks.
    So for instance I have
    myreports(1) = 2010 data
    myreports(2) = 2011 data ect

    So what I'm doing is
    Looping through the ID's
    looping through the workbooks (using dictionary K's)
    and running Application.Match(ID[x], workbook(k), 0, IDcolumn)
    and if there is a match I take that ID and put it in a new dictionary I'm creating to store the data I need.

    actual code
    fundraisingdata (matrix of current data)
    funreports (dictionary holding matricies of previous years data)
    size (calculates how big the array needs to be to store all the potential matchs)

    Please Login or Register  to view this content.
    So this code is rather expectantly taking forever to run. Can anyone think of a more efficient way to do this? I know I can do it manually comparing the data in Excel using VLookUp so I assume there has to be a better way?

  2. #2
    Registered User
    Join Date
    01-10-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Help speeding up matching a list of values over multiple workbooks

    anyone have thoughts on this?

  3. #3
    Registered User
    Join Date
    01-10-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Help speeding up matching a list of values over multiple workbooks

    Okay changed my code to

    Please Login or Register  to view this content.
    which is a bit faster. Also asked here

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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