Slow Paste On Array Locked Formulas

[mdbrierley]

Hi folks!

I need your help please!

I have a formula that counts the occurrence of a string within a column. When I run the formula, it does it very quickly, but then when I try to copy and paste as values, Excel takes forever!

I can't share the sheet, but the formula is basic abs looks like this:

=Countifs($B$1:B2,$B2)

The issue is that I have 600,000 rows of data that I can't cut down and have to break running the formula / copying and pasting into chunks which will take forever.

Is there a better formula or another way I can do this that won't take forever? I use this method every now and then (usually with less rows!) and a quicker way would be appreciated!!!

Thank you
Matt

 

[mdbrierley]

Solved! Just changed the amount of cells I was checking. Now just looking at the last 50 cells as there should be max 20 occurrences. Runs like lightning

 

[jmacleary]

Hi there. If you turn the calculating option to Manual that should help a lot.