Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() 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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#9
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#10
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Error message adaytum.xla when closing workbook in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 Error Message | Excel Discussion (Misc queries) | |||
error message during vlookup | Excel Discussion (Misc queries) | |||
Document Not Saved Error Message - Excel 2003 | Excel Discussion (Misc queries) | |||
VLOOKUP error message | Excel Discussion (Misc queries) |