Only one sheet very slow

[nisanthp]

Hi all,


Im facing a problem with one sheet in an xlsx file.

The file has 3 sheets 1 this sheet, one blank sheet for future data and another one with hadly 20 rows. The file size is 361 kb only.

The sheet contains almost 700 rows and column AT. Not all the columns are occuppied only labelled till AT. but all the columns are with filter. 3 columns are having a vlookup to next sheet. the sheet is responding in snail pace, not snail is better than this. this is happening with this sheet only,

wht cud be the reason. i searched this to check some similar disucssion was here but cudnt find in this forum.

pls help.

thanx in advance

 

[nisanthp]

@jkpieterse - Yes I can sort it its not a problem...cud u tell abt this fn

 

[jkpieterse]

Sure.
The trick is like this:
- First, do a INDEX/MATCH combined function, but do not require an exact match (this only works on a sorted list, hence the question)
- Next, compare the INDEX/MATCH result with the item you're looking for
- If the same, do the INDEX/MATCH, this time indexing the column you actually need the data from
- If different, return NA.

Suppose we're looking for the content of cell C1 in a list of values in A2:A100 and when found, we want the corresponding cell from B2:B100.

Normally, you would use:

=VLOOKUP(C1,$A$2:$B$100,2,FALSE)

Now we use:
=IF(C1=INDEX($A$2:$A$100,MATCH(C1,$A$2:$A$100,1)),INDEX($B$2:$B$100,MATCH(C1,$A$2:$A$100,1)),NA())

 

[jkpieterse]

I forgot to mention: this INDEX/MATCH trick on a sorted list is about 5 times faster than a regular VLOOKUP with FALSE as last argument.

 

[shg]

Or, same idea,

=IF(LOOKUP(C1, $A$2:$A$100)=C1, LOOKUP(C1, $A$2:$A$100, $B$2:$B$100), NA())

 

[nisanthp]

thnx Jan & Shj...

I will try these and reply...

 

[nisanthp]

Hi Jan,

Thanx for the fns, but cudnt try it..

the file has some other probs, it is taking ages to insert one row or to unhide rows or any single cell movement.

I removed all lookups, sumifs etc etc and just left with product formula only
still its taking looooong time.

I will attach the link to file here is it possible to chk abt probs..

http://www.adrive.com/public/2e57380ee887e23a0b8ebfda4c72a004fb224c52a41381fcd126b6a53f0c1614.html

thnx in advance....

 

[sous2817]

The file may be currupted. Have you tried recreating it ina new workbook to see if it has the same issues?

 

[mole999]

it is painfully slow, the one calculation page reduced to the top line even just crawls (corruption is possible)

 

[nisanthp]

i tried to copy its long time to paste but the new file also hangs. I saved this 2003 format but the size was whooping 15mb. If u notice the problem is with the single sheet.other 2 sheets are working normal. So can the whole file be corrupted

 

[mole999]

i've had a single page causing problems, one recommendation was to copy / cut the faulty page, paste to a new sheett, and delete the corrupted sheet.

I havent been able to do that with even just the top lines selected.

a four line vba i'm testing here is taking 3 seconds, to change four cells