(Msg. 1) Posted: Thu Oct 30, 2008 3:39 pm
Post subject: Vlookup takes too long to save Archived from groups: microsoft>public>excel>setup (more info?)
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?
(Msg. 2) Posted: Fri Oct 31, 2008 4:26 am
Post subject: RE: Vlookup takes too long to save [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
> 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
(Msg. 3) Posted: Fri Oct 31, 2008 10:20 am
Post subject: Re: Vlookup takes too long to save [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Hi Rick,
Show your formula
Is the data sorted?
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Rickoshae" <Rickoshae.RemoveThis@discussions.microsoft.com> wrote in message
news:C4B98F95-5A6B-49F5-BEE3-9CBD78026EE3@microsoft.com...
> 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
(Msg. 4) Posted: Tue Nov 04, 2008 5:53 pm
Post subject: Re: Vlookup takes too long to save [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
The data is not sorted in any manner...(actually, what do you mean by sorted?)
"Niek Otten" wrote:
> Hi Rick,
>
> Show your formula
>
> Is the data sorted?
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "Rickoshae" <Rickoshae.DeleteThis@discussions.microsoft.com> wrote in message
> news:C4B98F95-5A6B-49F5-BEE3-9CBD78026EE3@microsoft.com...
> > 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
>
(Msg. 5) Posted: Wed Nov 05, 2008 9:50 am
Post subject: Re: Vlookup takes too long to save [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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:
"Rickoshae" <Rickoshae.RemoveThis@discussions.microsoft.com> wrote in message
news:FEF2542E-657D-4FA1-AC35-1515DC58660E@microsoft.com...
> 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" wrote:
>
>> Hi Rick,
>>
>> Show your formula
>>
>> Is the data sorted?
>>
>> --
>> Kind regards,
>>
>> Niek Otten
>> Microsoft MVP - Excel
>>
>> "Rickoshae" <Rickoshae.RemoveThis@discussions.microsoft.com> wrote in message
>> news:C4B98F95-5A6B-49F5-BEE3-9CBD78026EE3@microsoft.com...
>> > 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
>>
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office