Excel - Calculating formula taking too long. Is there an alternative to the below formula.

[mrsushi]

I have the below formula which references another sheet and returns the total summary value on sheet lp99. However, the sheet(Paste LP99) has over 200,000 lines of data and the calculation takes near enough 45min - 1 hour to calculate. Is there a alternative which can be used in order to speed up the calculation? Ive heard arrays could work but not used these before?


SUMIF('PASTE LP99'!A:A, A43, 'PASTE LP99'!L:L)
SUMIF('PASTE LP99'!A:A, A43, 'PASTE LP99'!V:V)


Any help would be much appreciated

Many thanks
M

 

[mrsushi]

How about

Sub mrsushi()
   Dim Ary As Variant, Oary As Variant
   Dim Dic As Object
   Dim r As Long
  
   Set Dic = CreateObject("Scripting.dictionary")
   With Sheets("PASTE LP99")
      Ary = .Range("A2:L" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   For r = 1 To UBound(Ary)
      Dic(Ary(r, 1)) = Dic(Ary(r, 1)) + Ary(r, 12)
   Next r
   With Sheets("sheet1")
      Ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value2
      Oary = .Range("AA2:AA" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   For r = 1 To UBound(Ary)
      Oary(r, 1) = Dic(Ary(r, 1))
   Next r
   Sheets("Sheet1").Range("AA2").Resize(UBound(Oary)).Value = Oary
End Sub

Hi, just tried that and nothing has returned on sheet 1 in column AA2 down

Paste LP99 is the other sheet to which has the holdings in column L. Column A has Unique ID as a reference. Should there be a sumif formula above pls?

 

[Fluff]

Does col A on the output sheet have the same values as col A on the Paste lp99 sheet?
Also what sort of values are they?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[mrsushi]

Does col A on the output sheet have the same values as col A on the Paste lp99 sheet?
Also what sort of values are they?

Column A has Unique ID's (Sedols) ie stock identifier's. Column A in Paste Lp99 has these stock ID's but may contain more than one of the same Sedols.

 

[mrsushi]

What version of Excel are you using?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I'm using Microsoft Excel 2010. Will update as per instructions. Regards

 

[Fluff]

What do the Sedols look like?

 

[mrsushi]

What do the Sedols look like?

Please see below

Apologies for the formatting

619673​

619725​

619783​

619784​

619785​

620563​

620570​

620598​

2966876​ 2967084​ 2967181​ 2967589​ 2967675​

B1G2932​

B1G2RD8​

B1G2SS0​

B1G3M58​

B1G40S0​

B1G40W4​

B1G4262​

B1G4YH7​

B1G5DX9​

B1G5FB1​

B1G5HX7​

B1G5XP1​

 

[Fluff]

Ok, those shouldn't cause a problem. Are you saying that after running the macro the entire col AA on the output sheet is blank?
Also what is the name of the output sheet?

 

[mrsushi]

Ok, those shouldn't cause a problem. Are you saying that after running the macro the entire col AA on the output sheet is blank?
Also what is the name of the output sheet?

HI, thats right after running the macro, the column AA is blank. The output sheet is called "Available Prices"

 

[Fluff]

Did you change both instances of Sheets("Sheet1") to Sheets("Available Prices")