Question Excel Find/Replace performance

[Deleted member 2783327]

I have a spreadsheet with about 320,000 cells. Those cells contain formulas like ='R:\Basketball\[Ref Stats v8.xlsx]Analysis Data'!$t1465
I reference all external content only in one sheet and all others work from that data so other sheets don't have to use external references.
Pretty basic stuff.
I am doing a find/replace on this sheet replacing Ref Stats v8.xlsx with Ref Stats v9.xlsx
It's been going for 23 hours.
When I was using excel 2010 it took 6 hours.
My CPU usage is ok. My temps are ok.
Might I have a problem, or is this normal for excel 2016 to be so slow at find/replace?

 

[Lafong]

Sounds like a good reason for me to stick with Excel 2010.

Have you otherwise noticed 2016 is unusually slow?

 

[Ralston18]

Update your post to include full system hardware specs and OS information.

The hardware capabilities may be a contributing factor as well as the version of Excel.

= = = =

Yes, you have a problem and perhaps much deeper than you realize.

You need to use a database.

Excel has database like functions and features but Excel is not a database.

And the problem you describe is a classic example of what happens when an Excel spreadsheet(s), worksheets, etc. gets too big.

320,000 cells = too big.

Overall, you will need to either import the Excel spreadsheets data into Access tables and/or use the Excel tables (Read only) as the "back end" for an Access database.

How simple, straightforward, difficult or cumbersome that may be depends on the current Excel spreadsheets. Most Excel "stats" tables end up being quite complicated.

Which is likely the breaking point where matters now stand.

Current structure (rows, columns), and consistency?

 

[Wolfshadw]

What I would try to do (no promises on success rate), would be to export the file to a .csv.
Then I would copy and paste that to a word file.
I would then perform the search and replace (seems to work better in word, rather than excel).
Then do the reverse to get it back into excel.

Again, no promises on the success.

-Wolf sends

 

[Deleted member 2783327]

Sounds like a good reason for me to stick with Excel 2010.
Have you otherwise noticed 2016 is unusually slow?

Yes, it just seems generally slower overall. Opening files, calculations (I set it to manual so it doesn't slow me down all the time), saving files etc. But as @Ralston18 said, maybe there are other issues at play.

 

[Deleted member 2783327]

Update your post to include full system hardware specs and OS information.

The hardware capabilities may be a contributing factor as well as the version of Excel.

Sometimes I forget where I am. I thought my hardware specs were part of my profile. Sorry

I9-10940X @ 4.1ghz
32GB F4-3733C16Q-32GTZN ram
970 EVO Plus 2tb data drive (Connected to CPU, not via chipset).
RTX 2080 Ti GPU
Corsair HX1200i PSU
MSI Creator X299 motherboard
Corsair H115i 280mm cooler 4 fans push-pull
Thermaltake W100 case with 11 case fans
10-G LAN with 10G switches. All infrastructure is 10G.
2xPhillips HDR400 32" 1440p IPS Monitors
Windows 10 1809 LTSC patched to 2021-10. (still testing 2021-11).
All Office 2016 patches up to date.
Razer Lancehead mercury edition mouse
Logitech G105 keyboard.

Yes, you have a problem and perhaps much deeper than you realize.
You need to use a database.

I know. I haven't had time to build one. I have to reinstall CF10 on my server, install mysql, code the apps etc. I've made several attempts at starting but end up continuing to use Excel coz I just need to get stuff done.

Excel has database like functions and features but Excel is not a database.

Understood

320,000 cells = too big.

Lol, I thought 2000 rows x 160 columns was small

Overall, you will need to either import the Excel spreadsheets data into Access tables and/or use the Excel tables (Read only) as the "back end" for an Access database.

Don't use Access any more due to several issues. It was some years ago, I can't remember what the issues were... I think it might have been related to ODBC performance issues (the data was too big for Access), and various Colfusion connector issues.

How simple, straightforward, difficult or cumbersome that may be depends on the current Excel spreadsheets. Most Excel "stats" tables end up being quite complicated.

These have evolved to a large suite of speadsheet files with some very complicated calculations over 9 years. part of what throws me off with the database side is figuring out how to convert excel formulas into cold fusion code. I'm sure it's possible, just aven't figured it all out yet.

Well, we've just been plunged into a 7th massive lock down and I'm going to be out of work for a few months at least, so I suppose I should make a concerted effort to write the database and apps.

Current structure (rows, columns), and consistency?

Not sure what you mean.
1 spreadsheet is linked to an external source file.
All other sheets do their stuff using that one sheet so there are no other external references.
Each row in the linked sheet is an officiating session. I have catered for 2000 sessions (Enough for about 11 years of data).
In this workbook the sheets provide various views of the source data.

 

[Deleted member 2783327]

What I would try to do (no promises on success rate), would be to export the file to a .csv.
Then I would copy and paste that to a word file.
I would then perform the search and replace (seems to work better in word, rather than excel).
Then do the reverse to get it back into excel.

Again, no promises on the success.

-Wolf sends

I'll give it a try. Thanks

EDIT: Actually, I don't think that can work, unless I can save the formulas to the csv and not the values.

 

[Alabalcho]

Why you ever need to change the data source' file name, instead of just having single file name for the data source, and copying over v8, v9 etc over it?

 

[Deleted member 2783327]

Why you ever need to change the data source' file name, instead of just having single file name for the data source, and copying over v8, v9 etc over it?

A mistake I made way back when I set up the project in the tracking system.
in v7 the source sheet was expanded to 2000 rows. So 7, 8 and 9 are the same size.
v7 - v8 took 5 hours on office 2010
v8 - v9 took 30 hours on office 2016.

And that 6 fold increase is why I posted - there has been no increase in the size of the source data or the structure of the source worksheet. All the columns are the same size, same order, same data type. The only relevant change I made in 2018 was to increase the source data from 1500 rows to 2000 rows.

In excel terms, formulas that work on ranges were changed from ..$A$5:$A$1500 to ...$A$5:$A$2000.

 

[USAFRet]

Sounds like a good reason for me to stick with Excel 2010.

Have you otherwise noticed 2016 is unusually slow?

This is not an Excel version issue, but rather a data construction issue.

 

[Deleted member 2783327]

This is not an Excel version issue, but rather a data construction issue.

Since 2018 the sheet has been 2000 rows x 160 columns. It has not changed in almost 4 years. See above reply.

 

[USAFRet]

These have evolved to a large suite of speadsheet files with some very complicated calculations over 9 years. part of what throws me off with the database side is figuring out how to convert excel formulas into cold fusion code. I'm sure it's possible, just aven't figured it all out yet.

Yep, this is a usual outcome.
Some function starts in Excel.
Over the years, it grows.

Eventually outgrowing Excel, and should have been ported into a database.

Excel is a great calculator.
It is not a great database.

 

[USAFRet]

Since 2018 the sheet has been 2000 rows x 160 columns. It has not changed in almost 4 years. See above reply.

What else changed?
Host system, network, whatever.

 

[Deleted member 2783327]

What else changed?
Host system, network, whatever.

Host system (server?) hasn't changed for well over a year.

My PC: Upgraded to 10940X from 7900X in Jan 2020 and updated Ram from 3200 to 3733 in June 2020 but been the same since. I upgraded to excel 2016 middle of this year.

Network; Nothing. It's been all 10G since late 2019

Spreadsheet: Originally I had formulas in the source sheet up to row 1840 (v7). I added the formulas up to row 2000 (v8) to cater for another year.

Over the past 2 years, with all the lock downs, I've only refereed about 25 times, instead 150 times per year. So the number of rows with actual data has only increased by 25.

Been trying to get MySQL going. I have an old license for ColdFusion 10, which I can't afford to upgrade. But it's giving me grief saying I need a commercial MySQL license (community version not supported). I have all the DB tables defined, and have got all the data into it, I just can't create the data source in CF10 so I can code the apps

Which jogged my memory from the last time I tried this a couple of years ago as to why I dropped the project and stayed with excel.

Update: I had been planning to go back to Server 2012 R2 because Server 2019 has some major issues with large file transfers and a number of other problems. I was also trying to get MySQL 8.0.27 working with Coldfusion 10 (I have to use the old version because it's the only version I have a license for).

Restored my Server 2012 R2 and MySQL 5.7.35 and viola... everything worked first shot.
All my file transfer and Server 2019 issues are gone. Things are flying along now.
My coldfusion and MySQL are now working well.
crazy stuff!