View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
[email protected] jmsla...@gmail.com is offline
external usenet poster
 
Posts: 9
Default 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