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]

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

I've rerun the macro and just uncovered that the macro did populate results. However, a number of cells havent pulled any figures in. The below is the macro used

Sub mrsushi1()

   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("Available Prices")

      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("Available Prices").Range("AA2").Resize(UBound(Oary)).Value = Oary

End Sub

 

[Fluff]

Ok, are those Serdols without values definately in the other sheet?
Also with values like B1G2932 could they also be like b1g2932 or will they always be upper case letters only?

 

[mrsushi]

Ok, are those Serdols without values definately in the other sheet?
Also with values like B1G2932 could they also be like b1g2932 or will they always be upper case letters only?

Hi. Sorry to keep you waiting. I've doubled checked the data on the LP99 Report. Its appears that its missing that data which is therefore returning nil. The formula appears to work fine but will need to do some more spot checks.

Thank you very much for your help in resolving this issue. Its cut down the processing time almost from 2 hours to 5 seconds. This will definitely stop my boss from roasting my backside and enable me to collate the data successfully.

Much appreciated and many thanks for your time this evening.

If there is an issue down the line, would this email thread still be around?

Regards
Martin

 

[Fluff]

Glad to help & thanks for the feedback.
Should you have a problem in the near future post back here, otherwise it would be better to start a new thread.

 

[mrsushi]

Glad to help & thanks for the feedback.
Should you have a problem in the near future post back here, otherwise it would be better to start a new thread.

Thanks Fluff.

One question i forgot to ask is which line of the code does the SUMIF part? Havent looked closely at Arrays before so trying to figure this out.

Many thanks
M

 

[Fluff]

This line is adding the values depending on the key in the dictionary (the value in col A)

Dic(Ary(r, 1)) = Dic(Ary(r, 1)) + Ary(r, 12)

 

[mrsushi]

This line is adding the values depending on the key in the dictionary (the value in col A)

Dic(Ary(r, 1)) = Dic(Ary(r, 1)) + Ary(r, 12)

Thank you.

 

[mrsushi]

Good afternoon,

Hope you are well.

Thankyou again for the the array formula above. I have another issue where some of the columns have returned a zero/blank value. Without making this too complicated, would you be able to provide an additional formula to look through the Column AA and look for blanks, then apply a SUMIF formula again but referencing ("Paste 99" sheet) Column B2:L column?

Macro at the moment is
1st Macro = Based on Column A(Available Prices) Sumifs A2: L (Sheet - Paste 99)

Additional Macro
2nd Macro = Look for any blanks in Cell AA and then base on Column B (Available prices - secondary ID), SumIF B2:L (Sheet - Paste99)

Many thanks
M

 

[Fluff]

Try

Sub mrsushi()
   Dim Ary As Variant, Oary As Variant
   Dim Dic As Object, Dic2 As Object
   Dim r As Long
   
   Set Dic = CreateObject("Scripting.dictionary")
   Set Dic2 = CreateObject("Scripting.dictionary")
   With Sheets("Paste 99")
      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)
      Dic2(Ary(r, 2)) = Dic2(Ary(r, 2)) + Ary(r, 12)
   Next r
   With Sheets("Available Prices")
      Ary = .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
      Oary = .Range("AA2:AA" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   For r = 1 To UBound(Ary)
      If Dic.Exists(Ary(r, 1)) Then
         Oary(r, 1) = Dic(Ary(r, 1))
      Else
         Oary(r, 1) = Dic2(Ary(r, 2))
      End If
   Next r
   Sheets("Available Prices").Range("AA2").Resize(UBound(Oary)).Value = Oary
End Sub

 

[mrsushi]

Try

Sub mrsushi()
   Dim Ary As Variant, Oary As Variant
   Dim Dic As Object, Dic2 As Object
   Dim r As Long
  
   Set Dic = CreateObject("Scripting.dictionary")
   Set Dic2 = CreateObject("Scripting.dictionary")
   With Sheets("Paste 99")
      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)
      Dic2(Ary(r, 2)) = Dic2(Ary(r, 2)) + Ary(r, 12)
   Next r
   With Sheets("Available Prices")
      Ary = .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
      Oary = .Range("AA2:AA" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   For r = 1 To UBound(Ary)
      If Dic.Exists(Ary(r, 1)) Then
         Oary(r, 1) = Dic(Ary(r, 1))
      Else
         Oary(r, 1) = Dic2(Ary(r, 2))
      End If
   Next r
   Sheets("Available Prices").Range("AA2").Resize(UBound(Oary)).Value = Oary
End Sub

Thank you very much. Initial calculation looks good .
Once again much appreciated.

Regards
M