why would a much faster computer run excel more slowly?

[brucebosler]

I process some big sheets where I'm looking through long arrays of text searching for certain strings, then extracting parts of those strings. Example

search for "*this string contains*" and it finds [data] this string contains what I want then pull data out from between the brackets and store, simple enough

It was taking a long time on my laptop, originally 15 minutes, so I put together a very past PC, i5 Sky Lake CPU overclocked to 4.5 GHz, DDR4 ram at 3.5GHz, M.2 SSD raid 0 with R/W 2.5GHz, etc. It absolutely smokes the laptop EXCEPT on Excel. In Windows 10 I tried 64 and 32 bit Excel but 64 bit is slower by a few minutes than 32


[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"][/TD]
[TD="class: td1"]load windows[/TD]
[TD="class: td1"] load wkbk[/TD]
[TD="class: td1"]process[/TD]
[/TR]
[TR]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[TD="class: td1"][/TD]
[/TR]
[TR]
[TD="class: td1"]laptop Win 7 spinner [/TD]
[TD="class: td1"]many minutes[/TD]
[TD="class: td1"] 30 sec [/TD]
[TD="class: td1"]4 min[/TD]
[/TR]
[TR]
[TD="class: td1"]PC win 7 spinner[/TD]
[TD="class: td1"]30 sec [/TD]
[TD="class: td1"] 25 sec[/TD]
[TD="class: td1"]10 min[/TD]
[/TR]
[TR]
[TD="class: td1"]PC Win 10 M.2 Raid 0[/TD]
[TD="class: td1"]10 sec[/TD]
[TD="class: td1"] 1 sec[/TD]
[TD="class: td1"]12 min[/TD]
[/TR]
</tbody>[/TABLE]


I was using FIND statements and got it down to 4 minutes on the laptop by changing all of the FINDs to MATCH, but still much slower on the very fast PC. I wrote the VBA code on the laptop and it has Office 2010 on it. The slower running fast PC has the latest from Office 365, could that be the issue? If so I would buy a copy of 2010 but don't want to waste the money if it could be another issue

thanks for any ideas

 

[MARK858]

Have you turned off the animation in 365 (I don't know why it would slow Excel down in this circumstance but it does slow down others so it might help).

 

[mole999]

There was a post the other day about why the cores weren't being utilised for parallel processing

> http://www.mrexcel.com/forum/genera...astest-handling-large-excel-calculations.html

 

[brucebosler]

but my cores are running faster on the new PC, 4500 vs 2500 so everything else equal it should be about twice as quick, and the memory is at least 3 times as fast, animations are off. Obviously everything isn't equal, need to figure out what it is

 

[mole999]

is the data being processed across all the cores. 64k will allow more memory to be utilised

 

[brucebosler]

I checked processes while it is running on the PC, It shows 4 cores active, about 50& CPU usage and 30% of 16G memory. I downloaded a gadget to monitor each core and if I only use 1 core by deselecting the other 3 in device manager that core goes to 100% usage and it slows down so it is using the others when activated

The faster running laptop only shows 30% CPU and about half of it's 4G

 

[mole999]

So the other avenue the thread followed was, is the code optimal ?

 

[brucebosler]

"is the code optimal?"

I guess that's always the $64K question , Since I got from 15 to 4 minutes by changing finds to matches, and it is really just looping to look for up strings and extract the data, it is a pretty simple routine. Define the range where the strings are, look for a match, strip out what you want, and paste to another sheet there's not a lot going on.

Besides, the question isn't is it optimal, it is why it processes in 1/3 the time on a computer that is many times slower.

 

[brucebosler]

I loaded Excel 2010 onto the faster machine and it now completes in 1:42 vs 4:00 with 2010 on the laptop and 12 min with 365 0n the PC. I don't have any idea why it runs 7 times faster on 2010 than Office 365 but at least I have a solution