Arrey formula taking too much time to calculate

[kedar limaye]

Hi,
i have made a file k2 Match_Final, in which tab "Current file" i have some arrey formula.
it works on data available in tabs "Current Address" and "Previous Address"
when i pull data into above to tabs it takes almost 2-3 minutes to calculate the "Current file".
I dont know what is the reason to take this much of time.
i tried changing the setting of formula calculation to Manual as well.​

 

[NARAYANK991]

Hi Kedar ,

Can you see if there is a reduction in time to recalculate ? Changing the setting of Formula Calculation to Manual should not be done.

The problem which I have not yet tackled is that you are repeating the HLOOKUP formula twice in each IF statement , checking to see if the result is 0 , and putting "" if it is. It is better to just have the formula once , and use cell formatting or the Options to not display zeros.

Narayan

 

[kedar limaye]

Thanks Narayan,

Actually do you know any better way to get the results what i have derived in lab "Current file"

Can you help me with that?

 

[SirJB7]

Hi, kedar limaye!
I tested it in 4 machines with different configurations ranging from i3 with 4Mb to i7 with 6Mb and calculation time was between 40" and 1'05". You said it took 2'-3', would you repeat the testing process it in other machines just to confirm times?
Regards!

 

[Haseeb A]

Hello Kedar,

In I6 to down DO NOT USE whole column reference. ie 'Previous File'!$B:$B, use a limited range like,

'Previous File'!B$1:B$1000 or use dynamic range.

=MATCH(1,('Previous File'!B$1:B$1000='Current file'!$B6)*('Previous File'!E$1:E$1000='Current file'!$E6),0)

Also in W6 to down.

=IF(V6="","",LOOKUP(2,1/(('Previous File'!B$1:B$1000='Current file'!$B6)*('Previous File'!F$1:F$1000='Current file'!$F6)),'Previous File'!A$1:A$1000))

Calculation was in <2 seconds.