Results 1 to 3 of 3
2014-07-23, 16:38 #1
- 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.
2014-07-23, 18:00 #2
- Join Date
- Jan 2010
- Fort McMurray, Alberta, Canada
- Thanked 61 Times in 59 Posts
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(*)
Group by CustomerNumber
Order by CustomerNumber
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.
2014-07-23, 18:20 #3
- Join Date
- Jun 2010
- Thanked 1,390 Times in 1,218 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.Rui