+ Reply to Thread
Results 1 to 7 of 7

Alternatives to looping through very large array

  1. #1
    Forum Contributor
    Join Date
    09-05-2011
    Location
    Essex, England
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    383

    Alternatives to looping through very large array

    Good Afternoon,

    wondering if anyone might be able to offer some alternatives.

    We have a table that in essence holds a snapshot of various metrics, across several lines by 15 minute period, by day - as you can imagine this is quite large, I believe there's some data missing as we had a little hiccup with the import mechanism (that's now resolved) and I've built the following routine to loop through and help identify if and where the gaps are so that we can reimport the missing data.

    Please Login or Register  to view this content.
    I've added commentary to illustrate the current bounds for the arrays but this will grow as other metrics (queues) are added and back filled, aside from increase the time between 'doevent' cycles is there a faster way of achieving the same result? at the moment its taking about an hour, maybe more to complete.

    I have tried a loop with a dcount on the source table but there was a 2 second(ish) delay which it was checked which I think would be just as long (if not longer) than this method.

    Thank you in advance.
    Last edited by Kramxel; 12-04-2018 at 10:03 AM.

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Alternatives to looping through very large array

    Why don't you just join them in SQL rather than looping through?

  3. #3
    Forum Contributor
    Join Date
    09-05-2011
    Location
    Essex, England
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    383

    Re: Alternatives to looping through very large array

    Hi Kyle123,

    My SQL knowledge is growing but isn't exceptional, I've used the odd join here and there, but nothing too spectacular, would you be able to give a little example so I can understand how this might work with this scenario?

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Alternatives to looping through very large array

    Not really without some sample data, I can't really follow what your code is supposed to be doing.

    Mock a sample workbook up and explain in plain English what you're actually trying to do - keep it simple, I'm easily confused!

  5. #5
    Forum Contributor
    Join Date
    09-05-2011
    Location
    Essex, England
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    383

    Re: Alternatives to looping through very large array

    Hi Kyle123 - I've attached a sample workbook and will try and keep it as simple as I can (shouldn't be too difficult as I'm easily confused too)

    Our Table has the several fields, the four included in this example and then several others, one for each metric we're monitoring.

    For each day, for each queue, for each activity period there should be an entry in the table, even if other than these 4 fields the value is 0.

    So I'm looking for a list of dates, queues and activityperiods that are missing from the table.

    As a example from this set the following should be returned (ReportingDate: 03/04/2017, Queue: QueueOne, ActivityPeriod: 00:15

    ::edit:: fwiw I've just done the math on my loop and it would something in the region of 215 billion loops taking c.3.5 hours to complete and I still need to incorporate a couple of new queues we've added.

    ::edit2:: I may have made this a little more complicated than it needs to be, its just occurred to me that each days activities for each queue are imported at the same time. i.e we get one feed for each queue for each day broken down into 15 minute increments, so in theory if we have a single ActivityPeriod entry for a day and queue it can be assumed we have them all. meaning we only need to check each queue is represented each day.
    Attached Files Attached Files
    Last edited by Kramxel; 12-04-2018 at 10:46 AM.

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Alternatives to looping through very large array

    To Be clear, you're using access?

    Would it be enough to know which hours were missing activity periods, if so you could use something like this:

    Please Login or Register  to view this content.
    If you need the actual interval, I don't know how to do it in Access (I use SQL server almost exclusively), however I'd have though that this would be far more efficient using a formula - just see if the row above is more than 15 minutes later than the one before - if it is you've found a missing interval - you could then filter/sort on that
    Last edited by Kyle123; 12-04-2018 at 10:59 AM.

  7. #7
    Forum Contributor
    Join Date
    09-05-2011
    Location
    Essex, England
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    383

    Re: Alternatives to looping through very large array

    Hi Again Kyle,

    Yes using Access currently as a POC for what we're trying to accomplish may well migrate to SQLServer after this.

    that's a good start i'll try that and see how we go, if nothing else i'll be learning something new!

    thank you.

+ 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. alternatives to excel for large volume of data
    By ajay1234 in forum Excel General
    Replies: 5
    Last Post: 05-28-2018, 04:20 PM
  2. [SOLVED] Speeding up code. Looping Alternatives?
    By ptmuldoon in forum Excel Programming / VBA / Macros
    Replies: 27
    Last Post: 08-01-2017, 11:55 AM
  3. Array/Sumifs Alternatives?
    By bchilme in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-10-2014, 11:09 AM
  4. Working with large dataset - alternatives to SLOW sumproduct?
    By Teebo in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 05-26-2014, 04:36 PM
  5. Ineffecient Looping - alternatives to coding?
    By MelissaGregory in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 03-11-2014, 09:42 AM
  6. array alternatives
    By martindwilson in forum The Water Cooler
    Replies: 9
    Last Post: 08-21-2013, 05:59 AM
  7. Looking for Alternatives... Use nested IF AND? Or LOOKUP? Or ARRAY?
    By lisach in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-18-2012, 01:33 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1