Optimizing the Formula to run faster Finding the latest record

Hi,

I have a sheet with more than 100k records. Service name is in column B, while the date it was scanned is available in columns j:o.

Formula in
column H will calculate if it is the latest record (based on the date for this service) (=IF(K2=AGGREGATE(14,6,K$2:K$89750/(D$2:D$89750=D2),1),"Yes","No"))
column I will calculate if the record is a latest one in that particular month. (=IF(K2=AGGREGATE(14,6,K$2:K$89750/(D$2:D$89750=D2)/(O$2:O$89750=O2),1),"Yes","No"))

Ex: ABC service would have been scanned for more than 2 years, so that last scan when it was done is calculated in H. irrespective of the month.
ABC service might have multiple scans in a particular month, the latest scan in that particular month is calculated in Column I .

While the formula is correct, the excel sheet hangs up when calculating the records multiple times .

Any operation i do, insert or edit takes huge amount of time and excel freezes, takes more than 8 minutes for just saving the file.

using Excel 2010.

Is there a way this formula can be optimized?

sample sheet is attached.


Appreciate the help.

You can try this alternate logic for a maximum If with multiple criteria. It may not perform much better than what you have already though as its an array formula, but worth a try: https://exceljet.net/formula/maximum...tiple-criteria

Aside from that some options are:
-switch to manual calculation mode
-upgrade to a newer version of excel that has MAXIFS formula
-calculating using VBA
-installing the PowerQuery add-in for Excel 2010.

Lastly, be careful with the logic for 'Latest in that Month' as you might not get the desired results if working across multiple years. If its an issue, you can change cell O2 to something like =TEXT(L2,"mmm yy") or =EOMONTH(L2,-1)+1

AGGREGATE will not be the cause of your problem... even over 13,000 rows. Something else in your file will be causing the issue. Do you have array formulae using whole column references... or SUMPRODUCT with whole column references, etc??

Please try
for Latest
=IF(COUNTIFS(D$2:D$89750,D2,K$2:K$89750,">"&K2)+(K2=0),"No","Yes")

Latest in that Month without Year
=IF(COUNTIFS(D$2:D$89750,D2,K$2:K$89750,">"&K2,$O$2:$O$89750,O2)+(K2=0),"No","Yes")

Latest in that Month with Year
=IF(COUNTIFS(D$2:D$89750,D2,K$2:K$89750,">"&K2,K$2:K$89750,"<"&EOMONTH(K2,0)+1)+(K2=0),"No","Yes")

Using Bo_Ry's formula from Column R gives correct results Using column K not J)

Originally Posted by JohnTopley

Using Bo_Ry's formula from Column R gives correct results Using column K not J)

Thank you Got it. But unfortunately this is also taking more than 5 minutes for the formula, and after that if i have to apply a filter or remove a filter it takes more than 5 min for each operation. Not sure if the issue is with Excel or my laptop now.

Thanks

What is your laptop spec?