Vlookup takes too long to save

[Rickoshae]

Hey guys,

I have a master file that holds prices and other data (text) which feeds
directly to 3 other files...If I update a price in the master file it
propogates through to the other files, using the vlookup function.
In theory this is great. However, when I save it, it takes too long...and I
mean long...each file has around 20,000 lines...

I am using Vista with 2007 office.

If I combine each file as a worksheet onto the same workbook...the file size
become around 50meg...and unworkable...any ideas?

Thanks, Rick

 

[Niek Otten]

Hi Rick,

Show your formula

Is the data sorted?

 

[ShaneDevenshire]

Hi,

One problem is that VLOOKUP is resource intensive, so having a lot of them
can really impact performance.

There are lots of things you can try, for example you could put the workbook
into manual recalculation mode before saving and dispable the option to
recalculte before saving. You might be able to write more efficient
formulas, but we would need to see the current formulas. For example, If you
have formulas like this
=IF(ISNA(VLOOKUP(A1,Table,3,FALSE)),"",VLOOKUP(A1,Table,3,FALSE)) you can
simplify them in 2007 to
=IFERROR(VLOOKUP(A1,Table,3,FALSE),"")

Here is a detailed discussion of how to improve performance in Excel 2007

http://msdn.microsoft.com/en-us/library/aa730921.aspx

 

[Rickoshae]

formula is as follows:

=VLOOKUP(A3, 'C:\Users\rickj\Documents\Master Price
Files\[master.xlsx]Main'!$A$3:$AS$19080, 1, FALSE)

The data is not sorted in any manner...(actually, what do you mean by sorted?)

 

[Niek Otten]

Hi Rick,

With your formula you don't actually look up any additional data, you just
return the search argument you used. I suppose there are other formulas
where you look up a second (or other column).
I also suppose the A column is a product number or other unique
identification.
If you would sort the data ascending on the productnumber, you can instruct
Excel to use an intelligent search (like binary search) insted of just
looking through 20K product nembers. That could easily be a 100 times or
more faster.
=VLOOKUP(A3,'C:\Users\rickj\Documents\Master Price
Files\[master.xlsx]Main'!$A$3:$AS$19080,1,TRUE)
You'd then have to test wheter it returned a correct productnumber and if
so, use almost the same formula, but with a different column number,
otherwise return an error value.
I don't think combining all the data in one file should lead to such a large
file. Advantage is that your formula becomes less complex and you can use a
defined name instead of long sheet and cell references.
let's say that you define a name of "MyTable"to
'C:\Users\rickj\Documents\Master Price
Files\[master.xlsx]Main'!$A$3:$AS$19080.
Then your formula to look up the second column would be:

=IF(A3=VLOOKUP(A3,MyTable,1),VLOOKUP(A3,MyTable,2),"ERROR")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

formula is as follows:

=VLOOKUP(A3, 'C:\Users\rickj\Documents\Master Price
Files\[master.xlsx]Main'!$A$3:$AS$19080, 1, FALSE)

The data is not sorted in any manner...(actually, what do you mean by
sorted?)

Hi Rick,

Show your formula

Is the data sorted?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel