Speed up Formula

[elk03]

Hi - I have the formula below to count a data set and not count duplicates. It is incredibly slow. Any ways I may be able to speed this calculation up? Or change the formula? The data set is a table but the formula does not seem to work referencing the table column instead of the cells x2:x18881 for example.

=COUNT(IF(FREQUENCY(IF(('Collateral Listing'!$X$2:$X$18891=$B11)*('Collateral Listing'!$C$2:$C$18891="A")*('Collateral Listing'!$G$2:$G$18891="Y")*('Collateral Listing'!$AA$2:$AA$18891="Y"),MATCH('Collateral Listing'!$F$2:$F$18891,'Collateral Listing'!$F$2:$F$18891,0)),ROW('Collateral Listing'!$F$2:$F$18891)-ROW('Collateral Listing'!$F$2)+1),1))

 

[DanteAmor]

Hi @elk03 .

Thanks for posting on MrExcel.​

You could replace the formula with a macro. We would look for a macro that runs in a second. So every time you want to update the count, you just take a second to run the macro.

To make the macro you would need to comment out the following:

I see in your formula that you compare against cell B11, so B11 is on another sheet.

'Collateral Listing'!$X$2:$X$18891=$B11

a) What is the name of that sheet?

b) In which cell is the formula?

d) Do you need to copy the formula down?
Until where?
How far does the data in column B end?​

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[elk03]

Thanks for the response!

a) B11 is on the same sheet as the formula - called Summary
b) C14 has the formula
c) Yes, the formula is copied down to cell C28. Column B also goes down to B28

 

[DanteAmor]

a) B11 is on the same sheet as the formula - called Summary
b) C14 has the formula

It's strange, that the data starts in cell B11 and the formula in C14...
And then they both end up in row 28

Yes, the formula is copied down to cell C28. Column B also goes down to B28

Either way it is easy to adapt it in the macro. At the end of the macro is this line:

  sh1.Range("C14").Resize(UBound(c)).Value = c

Change the 14 to 11 if necessary.

----------------------------------------------------​

Put the following macro and run it:

Sub replaceformula()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim dic As Object
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long
 
  Set sh1 = Sheets("Summary")       'Fit to the name of the sheet where the formulas go
  Set sh2 = Sheets("Collateral Listing")
  Set dic = CreateObject("Scripting.Dictionary")
 
  a = sh1.Range("B11", sh1.Range("B" & Rows.Count).End(3)).Value
  b = sh2.Range("A2:AA" & sh2.Range("F" & Rows.Count).End(3).Row).Value
  ReDim c(1 To UBound(a, 1), 1 To 1)
 
  For i = 1 To UBound(b, 1)
    If b(i, 3) = "A" And b(i, 7) = "Y" And b(i, 27) = "Y" Then
      If Not dic.exists(b(i, 24)) Then Set dic(b(i, 24)) = CreateObject("Scripting.Dictionary")
      dic(b(i, 24))(b(i, 6)) = Empty
    End If
  Next
  For i = 1 To UBound(a, 1)
    If dic.exists(a(i, 1)) Then c(i, 1) = dic(a(i, 1)).Count
  Next

  sh1.Range("C14").Resize(UBound(c)).Value = c
End Sub

Please, take the execution time with all your data and tell us.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[elk03]

Thanks alot for the help! Seems to working, and is very quick. Thanks!