Array formula for lookup a max value cost too much time
Dear Excel friends,
Still struckling with a formula which cost a lot of time to run because of the amount of records (appr 50.000). When runs 'calculate' it cost many many minutes that I have to wait :(
I'm using below formula based on array (Ctrl+Shift Enter)
{=MAX(IF(I:I=I2;Y:Y))}
Because it reports a zero when nothing found in column Y it's changed to
{=IF(MAX(IF(I:I=I2;Y:Y))=0;"";MAX(IF(I:I=I2;Y:Y))) }
Both formulas spend too much time when calculate.
Because I'm using this type of formula also in other columns you understand that this way of doing it is a lot of 'calculation waiting time'.
Is there a faster way for this formula ? (Another formula or VBA code).
Regards, Johan
|