# Multi Core Processor with Max Memory



## amerifax (Jun 8, 2011)

If I purchase a motherboard that uses a Hex-Core Processor (I think that is the proper term) and max out the Memory on the motherboard, will that enable me to process extremely large files in Excel or Access, just for example.

I have files with a Million plus records and they are just going to keep growing.  I need to be able to handle the data processing without waiting 20-30 minutes for a formula to calculate.

Any suggestions or help would be appreciated.

Thanks,

Bob


----------



## sous2817 (Jun 8, 2011)

I'd say, generally speaking, the faster your processor, the more cores it has, the more memory it has, etc the faster the program will load / run.  If you're pushing 1,000,000 lines of data, no matter how high-end your computer is, Excel can't handle it due to a limitation within the program.


----------



## MrKowz (Jun 8, 2011)

As sous says, a computer with more processor cores will definitely assist in excel calculations, however there is still a limitation to how fast it can go.

At home I run a mid-line Intel i7 processor (4 physical cores, 8 virtual cores) and 4GB DDR3 RAM, and I can definitely tell a WORLD of difference between that and my single core, 3GB RAM computer I use at work when it comes to worksheet calculations.  As long as you have Excel 2007 or Excel 2010; and Windows Vista or Windows 7, then Excel can utilize the multiple core processing.

Another thing that can greatly affect worksheet calculation is the actual structure of the formulas, and what formulas you are using.


----------



## amerifax (Jun 8, 2011)

We are just over a Million records by about 10,000, as said above.  We are currently using a Dual Core (64-bit) Processor with 4GB of RAM and 4GB with the ReadyBoost (not sure if that has any bearing at all).

This is the formula we are running:

=IF(COUNTIF(M:M,M2)>1,"X","")

And it takes hours.

It was mentioned a million lines...Does that have any bearing on Physical Size;  such as 5 columns 1,000,000 records or 10 columns 500,000 records?

Thanks,

Bob


----------



## Norie (Jun 8, 2011)

Bob

If a relatively simple formula like that is taking that amount of time there must be something else going on.

That's probably other formulas, so it's probably worth into their structure etc as MrKowz suggests.

Personally I would be looking at use something other than Excel for that amount of records.


----------



## amerifax (Jun 8, 2011)

>>Norie<<
You mean Access or is there something else out there?


----------



## MrKowz (Jun 8, 2011)

Normally that formula wouldn't be that bad, but since you are using it on a million plus lines, that means you are evaluating that IF and the COUNTIF formula over a million times each.  We might be able to halve the amount of calculation time (due to that formula) by making the following adjustment:



Change your formula to =COUNTIF(M:M,M2)-1
Highlight the column that your formula is in
Change the custom number formatting to "X";"";""
What this does is store a numerical value in the cell, but SHOW it as an "X" for where it finds 2 or more of the same record, and show a blank for all other values.


----------



## Norie (Jun 8, 2011)

Bob

There's plenty of other things out there, it really depends what sort of data you are dealing with, what you are trying to do etc.

Even 1 million rows/records might be pushing Access a bit.

Perhaps you should even consider storing the 'raw' data outwith Excel.


----------



## amerifax (Jun 8, 2011)

Norie said:


> Perhaps you should even consider storing the 'raw' data outwith Excel.



Raw data you must mean as a text File? Out side of Excel do you mean as a external file or something other than Excel?


----------



## amerifax (Jun 8, 2011)

MrKowz said:


> Change your formula to =COUNTIF(M:M,M2)-1
> Highlight the column that your formula is in
> Change the custom number formatting to "X";"";""



I will run this within a couple of hours. Just finishings with the long one, at least I hope in a couple of hours.


----------



## amerifax (Jun 8, 2011)

If I purchase a motherboard that uses a Hex-Core Processor (I think that is the proper term) and max out the Memory on the motherboard, will that enable me to process extremely large files in Excel or Access, just for example.

I have files with a Million plus records and they are just going to keep growing.  I need to be able to handle the data processing without waiting 20-30 minutes for a formula to calculate.

Any suggestions or help would be appreciated.

Thanks,

Bob


----------



## amerifax (Jun 8, 2011)

MrKowz

Should my formula look like this??

=IF(COUNTIF(M:M,M2)-1,"X","")

Then change the custom number format after running the formula?

Just wanted to make sure before I ran it.

Thanks for your help so far, I really appreciate it.

Bob


----------



## MrKowz (Jun 8, 2011)

No, we are eliminating the IF statement altogether.  Make the formula:

=COUNTIF(M:M,M2)-1

And apply the custom format.

What this does, is it actually creates a series of numbers that are either 0 or greater than 0.


----------



## Norie (Jun 8, 2011)

Bob

I don't know enough about your setup to suggest something in particular.

It just seems to be far too much data for Excel to deal with efficiently.

Pretty sure some sort of database would be involved.


----------



## amerifax (Jun 8, 2011)

>>MrKowz<<

It seems I'm over loading Excel. I get a message to shorten the about of records I'm tiring to process.
Excel "Cannot complete task with available resources. Choose less data or close other programs."

Can I run several thousand records at a time. In other words is the formula good at one line at a time. That would let me past and run several times in the column tell I get to the bottom.


----------



## Norie (Jun 8, 2011)

Bob

If you are dealing with millions of records I don't think Excel  it's the best tool for the job.

Do you have anything else you can use?


----------



## amerifax (Jun 8, 2011)

Norie said:


> Do you have anything else you can use?



I wish I did. Can you give me any ideas?


----------



## amerifax (Jun 11, 2011)

>>Norie<<
I've been hearing then at. But I don't have a clue as to what else is out there. I am all ears or should I say all eyes.

Would it possibly be something like Oracle. which is a database. I do have to agree. We are definitely pushing the limits of Excel. One of my calculation started Friday four o'clock in the after noon and here it is Saturday morning 2:22 AM and it's at 89%. I haven't had days like this since dBase III which used to take three days, which now takes an hour and a half to run my bread and butter program.
Bob


----------

