Find and replce takes 5-6 hours

[Yanta]

Like any software developer, when I make significant changes to my spreadsheet I increment the version (So Ref Stats V7 becomes Ref Stats V8. I then have to change the references in one sheet for a couple of different files. Each file takes up to 6 hours to replace the references. (There's about 15,000 references to be updated and that grows by about 2,500 per year).

I have a HEDT system running 32GB of RAM with a way overclocked CPU and 970 Pro SSD and I still can't get the time down.

Is there a way to reduce the time it takes to update the references without "Don't rename the file"?

thanks

 

[Akuini]

How did you do it?
If using macro the can you show us the code?

 

[Yanta]

Ctrl-F, replace, put in "find what" and "replace with" set options to Within: Sheet, Look in: Formulas, click Replace All.

 

[Akuini]

Well, you'll need a macro to do that.
Can you put the list somewhere in 2 column contains:
in first column: the existing string/reference to be replaced.
in second column: the new string/reference.

Also can you post some examples of the formula & also the expected result?

 

[jimrward]

Have you tried disabling excel auto calculation do the find replace and enable calculations again

 

[Yanta]

Slight miscalculation on the number of references replaced... ~41,000

Auto calculate is always turned off.

If I click the excel icon on the taskbar Excel crashes. If I click on the Excel display Excel crashes. It's just a matter of starting the replace and waiting for it to finish.

Formulas are many and varied but they are links to another spreadsheet '\\server\tanya\basketball\ref stats v7.xlsx.

One cell could have 8 references to that spreadsheet. After the replace, the V7 becomes V8, and next time it will become V9 and so on. The sheet being updated has 1840 rows and 300 columns. The work book has 10 sheets and several of them have references to be replaced. The total time for all sheets except the 'summary' sheet (the big one), is 15 minutes.

 

[jmacleary]

Like any software developer, when I make significant changes to my spreadsheet I increment the version (So Ref Stats V7 becomes Ref Stats V8

Hi Yanta. Like you I am a software developer and fully appreciate the need for version control. However, can't you get round the issue by always having the latest version called Ref Stat Current, and taking backup copies of each previous version?

 

[Yanta]

Yes, that would seem to be the only solution.
And embed some version metadata in the spreadsheet - as there are always minor revisions (v7.1.1, v7.2.0 and so on).

My original concern was that something was "broken" in excel that was causing the extended times. Perhaps that's just how it is.

Gosh, imagine doing this 5 years ago on a mainstream computer... Would take days

 

[jimrward]

Excel being smarter than is sometimes required is probably trying to evaluate and qualify each link and establish that link hence the delay so version control outside of excel makes sense