Macro Needed To Speed Up A Formula

**JimmiOO** · 03-19-2012, 10:27 AM

I have a formula that works very well, the problem i have is it keeps crashing on me when i try to sort a lot of data. The formula works ok on a few hundred rows but i need it to do over 20.000 rows. Would it be possible for a Macro to do this? and to spreed things up?

I have added the formula i use below.

Please Login or Register  to view this content.

**Domski** · 03-19-2012, 10:34 AM

Trying setting calculation to manual before performing your sort and then turn it back to automatic afterwards.

Chances are your Excel hasn't really crashed, it's just struggling a bit.

Dom

**JimmiOO** · 03-19-2012, 10:52 AM

Hi Dom

Thanks for the reply, i have already done that but still getting the same thing, it just seems to crash then i get the not responding thing coming up.

**Domski** · 03-19-2012, 11:00 AM

You shouldn't if calculation is set to manual but to be honest 20,000 sumproduct formula is probably not the best idea. When I'm dealing with that sort of volumes of complex calculation I would probably work out another way of doing what I wanted. Can you get rid of the formula once it's calculated?

For info often when Excel says it's not responding it doesn't mean it's crashed. If you leave it long enough it will generally come back to life once it's finished calculating. You're just asking it to do one hell of a lot sorting data that contains calcs like that.

Dom

**JimmiOO** · 03-19-2012, 11:33 AM

Hi Dom, i can copy the formula all the way down when it is switched to manual but it does not cope when i turn it on, i can get rid of the forumla when the calc is done if needed. When it said it was not responding i left it for over 20mins but no luck.

I am not to sure what other ways there is of doing this calc? any ideas?

**Domski** · 03-19-2012, 11:42 AM

Pivot tables can be good for doing this sort of calc. You can use GETPIVOTDATA formula to pull information from the pivot into other data if required. Otherwise as suggested do let it calc once and then copy...paste special...values the results. You might need a faster pc

Dom

**royUK** · 03-19-2012, 11:49 AM

have you tried using a Pivottabel instead of sumproduct?

**JimmiOO** · 03-19-2012, 12:00 PM

Cheers guys, i have never used the GETPIVOTDATA before, I am not that sure where to begin with it but i will give it a go. Maybe a quicker PC may work lol

**Domski** · 03-19-2012, 12:13 PM

The easiest way to see an example of a GETPIVOTDATA formula is to create your pivot and then in a spare cell pop an = and select one the data items in the pivot. It should give you the syntax to pull that item which you can then edit to reference cells.

Dom