I have two vlookup functions in one spreadsheet linking data from two other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close other
applications." My IT department is saying that I should query data in a table
(access) rather than storing data of 20K + rows using the Vlookup in excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?

When you say spreadsheets, do you mean files or workbooks? If so, Excel does
not like large external databases.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kim" wrote in message
...
I have two vlookup functions in one spreadsheet linking data from two other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close
other
applications." My IT department is saying that I should query data in a
table
(access) rather than storing data of 20K + rows using the Vlookup in
excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?

Kim;462915 Wrote:
I have two vlookup functions in one spreadsheet linking data from two
other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close
other
applications." My IT department is saying that I should query data in a
table
(access) rather than storing data of 20K + rows using the Vlookup in
excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?

I doubt Excel 2003 would be able to handle 600k rows since there's a
limit per sheet of about 65k rows, however I imagine Excel would easily
be able to handle 20k+ rows for a VLookUp, the question is, can the
machine it's running on do so?


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=128019

files

"Don Guillett" wrote:

When you say spreadsheets, do you mean files or workbooks? If so, Excel does
not like large external databases.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kim" wrote in message
...
I have two vlookup functions in one spreadsheet linking data from two other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close
other
applications." My IT department is saying that I should query data in a
table
(access) rather than storing data of 20K + rows using the Vlookup in
excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?

Check out this link on memory and calculations...

http://www.decisionmodels.com/index.htm

If you really mean 600k rows then you are defintly in trouble here. If you
meant 60k rows from an external spreadsheet then you are still in a bit of
hot water but you might be able to tweak things to make them work.

As opposed to a vlookup which has pile of overhead you could look at using
an external database query to the sheets involved (a much smaller footprint).

--
HTH...

Jim Thomlinson


"Kim" wrote:

I have two vlookup functions in one spreadsheet linking data from two other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close other
applications." My IT department is saying that I should query data in a table
(access) rather than storing data of 20K + rows using the Vlookup in excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?

That's exactly what I questioned and my IT Dept says that the machine Dell
Optiplex 755 with 2 GB of RAM, intel core 2 vpro processor, should be able to
handle the amount of data I am dealing with, but they said that excel Vlookup
should not be the tool I use to do my data comparison (I don't buy it).

I am trying to determine if the error I am receiving is my machine or the
Vlookup function.

"p45cal" wrote:


Kim;462915 Wrote:
I have two vlookup functions in one spreadsheet linking data from two
other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close
other
applications." My IT department is saying that I should query data in a
table
(access) rather than storing data of 20K + rows using the Vlookup in
excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?

I doubt Excel 2003 would be able to handle 600k rows since there's a
limit per sheet of about 65k rows, however I imagine Excel would easily
be able to handle 20k+ rows for a VLookUp, the question is, can the
machine it's running on do so?


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=128019

I used 600K rows as an example of what I thought excel could handle using the
Vlookup function. My current situation is only dealing with 23K rows of data.
I will check out this link you supplied, thanks.

"Jim Thomlinson" wrote:

Check out this link on memory and calculations...

http://www.decisionmodels.com/index.htm

If you really mean 600k rows then you are defintly in trouble here. If you
meant 60k rows from an external spreadsheet then you are still in a bit of
hot water but you might be able to tweak things to make them work.

As opposed to a vlookup which has pile of overhead you could look at using
an external database query to the sheets involved (a much smaller footprint).

--
HTH...

Jim Thomlinson


"Kim" wrote:

I have two vlookup functions in one spreadsheet linking data from two other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close other
applications." My IT department is saying that I should query data in a table
(access) rather than storing data of 20K + rows using the Vlookup in excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?

if you have 600K rows of data, your IT is correct to suggest using a database
application. However, for 20K rows ++ of data, Excel can handle that.

Try closing other applications that are running at the same time
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Kim" wrote:

I have two vlookup functions in one spreadsheet linking data from two other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close other
applications." My IT department is saying that I should query data in a table
(access) rather than storing data of 20K + rows using the Vlookup in excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?

Check out the link I sent you. It is not a machine issue. XL has memory
limits that you will reach long before you reach your machines limit. Your
problem is that vlookup has huge overhead especially if you are looking at
external files. Relational databases handle this kind of thing MUCH better.
--
HTH...

Jim Thomlinson


"Kim" wrote:

That's exactly what I questioned and my IT Dept says that the machine Dell
Optiplex 755 with 2 GB of RAM, intel core 2 vpro processor, should be able to
handle the amount of data I am dealing with, but they said that excel Vlookup
should not be the tool I use to do my data comparison (I don't buy it).

I am trying to determine if the error I am receiving is my machine or the
Vlookup function.

"p45cal" wrote:


Kim;462915 Wrote:
I have two vlookup functions in one spreadsheet linking data from two
other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close
other
applications." My IT department is saying that I should query data in a
table
(access) rather than storing data of 20K + rows using the Vlookup in
excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?

I doubt Excel 2003 would be able to handle 600k rows since there's a
limit per sheet of about 65k rows, however I imagine Excel would easily
be able to handle 20k+ rows for a VLookUp, the question is, can the
machine it's running on do so?


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=128019

Closing other apps will not help. Check out memory at the following link...

http://www.decisionmodels.com/index.htm

--
HTH...

Jim Thomlinson


"Francis" wrote:

if you have 600K rows of data, your IT is correct to suggest using a database
application. However, for 20K rows ++ of data, Excel can handle that.

Try closing other applications that are running at the same time
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Kim" wrote:

I have two vlookup functions in one spreadsheet linking data from two other
spreadsheets. I have 23K rows and I received an error of "Excel cannot
complete this task with available resources. Choose less data or close other
applications." My IT department is saying that I should query data in a table
(access) rather than storing data of 20K + rows using the Vlookup in excel.
Shouldn't excel be able to handle 600K rows of data using the Vlookup
function?