System specs for heavy processing

[SymphonyTomorrow]

Hello, my office is allowing me to get a desktop to offload data analysis off my laptop, which has been brought to its knees just doing data cleanup, much less calculations.

If you were building the optimal system for Excel-based data analysis, what would the specs be?

[NeedForExcel]

I use a Core I5 with 4 GB of RAM.

All things work fine.

However if you have the budget, go for a Core I7 and 8 GB of RAM.

Deep

[shg]

I would first consider the efficiency of the workbook design.

[SymphonyTomorrow]

It's data cleanup to start with, so there is no workbook design.

I've been collecting server session data from badly formatted reports and I'm trying to get them in a form that I can put into a pivot table.

Even just deleting rows across 700,000 lines has been completely locking up my spreadsheet, failing to complete after even more than half an hour running.

[shg]

I would respectfully disagree. Unless the workbook contains nothing but data, there is implicitly a workbook design. And if there is nothing but data, it should not be (terribly) slow.

[SymphonyTomorrow]

Always open to respectful disagreement, but I'm still struggling to practically apply the advice.

Here's how I get to my starting point:

A nightly routine generates reports automatically. They come grouped in three levels, an overall summary, a user summary, and user detail. The summary levels are offset by a column each. The overall summary is essentially a single line with a date at column A and a total at column H or so. The next level is a username, offset to column B and on its own row, with a total again in column H. The detail level is offset to column C but does not contain the username, only time stamps and duration, and is on its own row as well My goal is to analyze a year of data, which will be about 7 million lines, so I need just the detail. But the detail needs the username, which is stuck on a higher row. Finally, about half of the rows are blanks that need removing.

Furthermore, I have about 3,000 daily outputs like this. I'm using an add on that extracts the documents and compiles them into one document. Since there are many more lines than excel can handle, I'm planning to do cleanup on batches and ultimately create a massive CSV for analysis. At that point, Excel 64-bit will be necessary, but in this cleanup phase, my file size is sitting at about 11 MB, as I've only just begun.

After a batch has finished combining, I copy it into a new sheet just to be safe that there's no unknown baggage. As part of the combining process, file names are included, which are consistently named and contain a single word I need, so I use Find Replace to get rid of the excess. I also use it to get rid of headers that have repeated due to all the combined docs. Then I use select, F5, Special, blanks, to copy the username onto the detail line.

All that should be left is removing the blank lines. F5, blanks, right-click, delete, entire row.... And it hangs up and never frees until the process is ended in task manager.

So I've tried to provide some detail to help you determine how "workbook design" applies in context.

[SymphonyTomorrow]

I've attached a file showing all phases of my process. A snippet of my source document, then how it appears when I combine multiple files together, and then all the steps of my cleanup process.

Again, on the full-sized document which is in excess of 700,000 lines of this stuff, deleting locks things up.CleanUp Process.xlsx

[shg]

Thanks for taking the time to put that together to illustrate. It is indeed just data.

F5, blanks, right-click, delete, entire row.... And it hangs up and never frees until the process is ended in task manager.

1. I wonder if clearing the data would be faster than deleting rows, and then sorting to compress back to the top.

2. The process might be a LOT faster if you read the data into memory, operated on it there, and then wrote it back out at completion. Given the voluminous amount you have, and the finite size of arrays , it might need to be done in chunks.

This 'add-on' you use -- you folks write that?

My sense is that:

o more memory is good if you're peaking out (and 64-bit Excel would be a help to use even more), and would yield a big improvement if that's the bottleneck

o a faster processor offers limited performance improvement; maybe 2X, maybe even 3X, not the one or two orders of magnitude that you need.

[SymphonyTomorrow]

1. I wonder if clearing the data would be faster than deleting rows, and then sorting to compress back to the top.

You know, I had written that option off earlier because I hadn't aligned the user name with the log data, but as I now know how to do that, it becomes an option again.

2. The process might be a LOT faster if you read the data into memory, operated on it there, and then wrote it back out at completion. Given the voluminous amount you have, and the finite size of arrays , it might need to be done in chunks.

How would this be accomplished?

This 'add-on' you use -- you folks write that?

Now that I'm back at my machine, the Add-in is called RDBMerge, which I think I learned about on this forum some time back.

My sense is that:

o more memory is good if you're peaking out (and 64-bit Excel would be a help to use even more), and would yield a big improvement if that's the bottleneck

Yeah, I've been very confused by how only 25% of my memory is being used, even when the system is straining hard. Not sure how to allow Excel to utilize more.

Thanks for your help!

[Andy Pope]

You might find Filtering is quicker than Find

Also check the addin you use will work in 64bit excel as you can not have both 32 and 64 bit on the same pc.

[SymphonyTomorrow]

You might find Filtering is quicker than Find

Is this in response to me having used Find/Replace? If so, I did that because I needed to snip something out of the middle of a cell. Not sure how I'd accomplish that with filtering.

However, filtering might be a solution too, if I can filter, copy, and then paste into a new worksheet without all the blanks.

Also check the addin you use will work in 64bit excel as you can not have both 32 and 64 bit on the same pc.

Not sure, but the machine I'm talking about getting would be separate from my laptop, and I'd use remote desktop to do work on it, so I could use the RDBMerge Add-in on one and transfer the output to the other.

Thanks!

[shg]

Mr. Pope, as usual, has excellent suggestions.

[newdoverman]

I would go with an i7 processor, run 64 bit OS and 64 bit Excel, as much ram as you can use (operating system limited) 16 GB minimum, a 256 GB SSD. This is basically my system and it really flies.

[SymphonyTomorrow]

I would go with an i7 processor, run 64 bit OS and 64 bit Excel, as much ram as you can use (operating system limited) 16 GB minimum, a 256 GB SSD. This is basically my system and it really flies.

That sounds great. I was looking at true 8 core processors last night, and they're not terrible, especially if the rest of the system is rather bare bones. This sounds like a decent setup. I'd probably forego the SSD though, for the sake of expense.

[martindwilson]

is the use all processors option checked in excel options/advanced

[SymphonyTomorrow]

is the use all processors option checked in excel options/advanced

Indeed. Allprocessors.jpg

[martindwilson]

just a shot in the dark really as thats the default

[Andy Pope]

Without the actually code it's hard to know exactly how you where using the Find/Replace.

In your given example the file name is the same in every row. There for you only need to decode the first occurance,
from this
C:\Users\KEQ8455\Desktop\session_duration_custom_CODVUC01_516.xls
to CODVUC01. Then you can simply replace every cell, without finding and decoding again. Maybe that is what you are already doing.


In terms of filtering I used it for all stages of your example data reduction to identify empty rows, header rows.