CODEWITHSUNDEEP
excel
susja
susja

Reputation: 321

Improve Excel peformance for calculation

I'm using Excel 2010 for calculation of subset. I'm using a formula with COUNTIF and checking if any of 21,000 numbers from column B are among 300,000 numbers in column A. My system is running Win 7 with two processors 4 GB RAM but simply hangs doing the calculations. I can't do anything else while it calculates because it takes ~ 100% of CPU.

Is this calculation so CPU consuming or I could do something to avoid it?

Upvotes: 0

Views: 59

Answers (1)

Charles Williams
Charles Williams

Reputation: 23505

One fast way: Sort the 300000 numbers in column A and use the Double Vlookup trick to see if there are any exact matches: see this post on my blog for details of how to do it:

https://fastexcel.wordpress.com/2012/03/29/vlookup-tricks-why-2-vlookups-are-better-than-1-vlookup/

Upvotes: 2

Related Questions