I have a simple report I do every week, it only needs one formula;
=SUMIF(A:A,A2,B:B)
Which is fine, a nice simple formula. Takes no time at all to enter....until I copy it down 50,000+ rows, and it takes about 3-4 minutes to calculate it all making my computer a laggy space heater while it calculates...
Entering absolute references instead of entire column references, SUMIF($A$2:$A$50000,A2,$B$2:$B$50000), does not speed it up in any noticeable way (actually it seemed a little slower)
.
Can this be done (significantly) faster with VBA, or a different method I haven't thought of? A 3.24% faster method is of no use to me, I'm looking for 50%+!
If you need a sample just
=RANDBETWEEN(10,99) in A1:A50000
and
=RANDBETWEEN(50,500000) in B1:B50000
Then in C1
=SUMIF(A:A,A1,B:B)
And copy to the end
that goes about twice as fast as my actual data, but it still crawls along pretty slowly.
A warning on calculation speed, I don't know how much these details play into calculation speeds but if you are running on significantly lower hardware (like a Pentium M or something), it may take a really long time to calculate and excel might lock up, close any important spreadsheets just in case! I am on a Core i5 3320M with 8GB Ram and have Office Professional Plus 2010 64 bit.
Thanks!
Bookmarks