+ Reply to Thread
Results 1 to 2 of 2

Massive Statistical Number Crunching

  1. #1
    Registered User
    Join Date
    06-16-2008
    Posts
    8

    Massive Statistical Number Crunching

    I have been charged with the task of running some basic statistics calculations on a very large set of data in Excel 2007. Basically, I need to find the min, max, and average of the rows and columns, which isn't the hard part of the problem.

    The hard part is that the data is spread across 1300 xls files, and each one contains about 50,000-500,000 or more cells of data. I need a fast way to read all this in, do the math, and then export the results back into an Excel sheet. I'm afraid VBA would die trying to do this, or would take eternity. Or at least, if VBA was the best way, have someway of displaying the number of files it has finished so the user isn't left in the dark with no ETA.

    I've been wondering if using the Excel object in C# would be a better way to read the data in first, and then write a nice, neat Excel file afterwards, but I'm not familiar with Excel object, nor do I have Visual C# here with me at the moment.

    Any ideas?

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967
    VBA shouldn't have a problem with this provided that you close each of the 1300 workbooks before you open the next.

    I would suggest using the status bar to give an indication of progress.

    Please Login or Register  to view this content.
    for example.

    This will allow you to turn the screen updating off which which make the whole thing a lot faster.

    Please Login or Register  to view this content.
    Martin

+ 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