Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Las Vegas, Nevada, USA
    Thanked 0 Times in 0 Posts

    How much processing power do I need?

    How much processing power do I need to complete the attached spreadsheet quickly?

    I posted this question in the Excel Forum about a pc's requirements for completing a large Excel spreadsheet.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2010
    Fort McMurray, Alberta, Canada
    Thanked 83 Times in 79 Posts

    Wrong tool

    Might I suggest that you are going about this the wrong way? I read your original post in the Excel Forum although I have not taken a look at your spreadsheet file.

    If you throw more hardware at the problem you can expect a speedup of, at most, about 10x. Your hardware is excellent by the standards of 4 years ago so even a 10x improvement is likely out of reach. Also your software isn't too shabby either (XP 64-bit was not very popular but that's how you got support for 16 GB RAM).

    Your row count is very high and is getting near the limits of what Excel handle. The only way I can explain these astonishing recalc times is that you have a logic problem. Very likely Excel is scanning the entire list of rows as it processes the formula in each row. This would require Excel to process 826K^2 cells which is a huge number. It's also massively inefficient!

    In short you have a problem that databases were built for. My suggestion is to import this worksheet into Access. Create an index to enforce ordering on the column. Then run this query on the result:

    Select CustomerNumber, Count(*)
    From CustomerTable
    Group by CustomerNumber
    Order by CustomerNumber
    The names CustomerNumber and CustomerTable are the objects in Access that you define. If you use different names then substitute them into the logic above.

    You will get your answer in less than a minute, I'd estimate, and very likely under 30 seconds. That's with your current hardware and software and only changing your methodology.

    As I say, databases were built to handle these kinds of problems. With the indexing data Access knows how to efficiently process the database rows. Complete table scans are unnecessary because the query engine knows that identical CustomerNumber rows can only be nearby, directly adjacent to the current row. I'm using terminology here that's not relationally correct but I want to be clear about why and how a database can do this quickly and easily while a spreadsheet struggles so badly.
    Last edited by BHarder; 2014-07-23 at 18:12. Reason: Corrected row count. Added closing remarks.

  3. #3
    Join Date
    Jun 2010
    Thanked 1,401 Times in 1,223 Posts
    Attached you will find an Access database that does the counting you need. It has a table and a query, the query does what you need, but I ordered it by decreasing number of occurrences. The query takes just a few seconds in my i5 laptop.
    Attached Files Attached Files

Posting Permissions

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