Question for Debra Dalgleish / Reply to a Pivot Table Question

  • Thread starter Thread starter carl
  • Start date Start date
C

carl

Hi. Thanks for helping with my problem.

You mentioned a way to try and help the speadsheet to
calculate faster...

"If you could compare each row to a smaller range, the
formula would
calculate faster. For example, if the list is sorted by
date, and
there's a maximum of ten entries per date"


IF(SUMPRODUCT((OFFSET(A2,-MIN(ROW()-2,9),0,MIN(ROW()-
1,10),1)=A2)*(OFFSET(B2,-MIN(ROW()-2,9),0,MIN(ROW()-
1,10),1)=B2))>1,0,1)

What if there is a maximum of 6000 entries per date ? How
would I modify the formula ?

Thank you again.
 
The formula would be:

=IF(SUMPRODUCT((OFFSET(A2,-MIN(ROW()-2,5999),0,MIN(ROW()-1,6000),1)=A2)*(OFFSET(B2,-MIN(ROW()-2,5999),0,MIN(ROW()-1,6000),1)=B2))>1,0,1)

And if the old data won't change, you could copy this column, and paste
as values, once it's a couple of days old.
 
Back
Top