| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Super slow table array formulas
I have a data entry sheet were
sheet 1 has the table array fromulas built down 25000 and accross about 12 columns. The table is about 500,000 rows of 12 colums. It was running ok, very sluggish) I have 2.888 G processor on the computer. But I stuck in a =if(countif( formula to check for duplicates. Now this sheets is running so slow it is almost unuseabel. any sujjestions |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Super slow table array formulas
Can you break up your ranges into smaller groups. I had to correct one for a
client recently where he was using a VLookup for 65000 rows. If desired, I will take a look if you send to my address below. -- Don Guillett Microsoft MVP Excel SalesAid Software "Dylan @ UAFC" wrote in message ... I have a data entry sheet were sheet 1 has the table array fromulas built down 25000 and accross about 12 columns. The table is about 500,000 rows of 12 colums. It was running ok, very sluggish) I have 2.888 G processor on the computer. But I stuck in a =if(countif( formula to check for duplicates. Now this sheets is running so slow it is almost unuseabel. any sujjestions |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Super slow table array formulas
sure I can send.
Its a huge file I could email. The basics is, The data is driven off a unique phone #. Wich is the table array. It had to done because we really have to many sloppy data entry and no speed. So I wanted it to popluate the Address, city state zip county, name ect. 1) do you think the speed would be higher If i had the table array as a seperate file and not just on sheet2, 500,000 row and need to built to add additional data 2) I have the formul set to =IF(ISNA(VLOOKUP(G4,Sheet1!$A$1:$J$500000,3,FALSE) ),"",VLOOKUP(G4,Sheet1!$A$1:$J$500000,3,FALSE)) because I hate those #n/A's I did notice this was a tremdous accomplishmen for the computer to copy down, wich took 25 min. The next issue I was having was duplicate data entry wich I then used the formula =IF(COUNTIF($G$4:$G$25000,G6)1,"FALSE"," TRUE") I needed it to render Treu is in fact the data was clean and new and then render False is the appointment was in fact a dup. If you could show me how this formula would leave the cell empty until data is typed in the corrisoponding cell, that would great. I have tried a # of way/ In sheet one there is another table array formula hidden in sheet one, but the range is literally r4:s12. that could not be putting to much strain. Any suggestions. "Don Guillett" wrote: Can you break up your ranges into smaller groups. I had to correct one for a client recently where he was using a VLookup for 65000 rows. If desired, I will take a look if you send to my address below. -- Don Guillett Microsoft MVP Excel SalesAid Software "Dylan @ UAFC" wrote in message ... I have a data entry sheet were sheet 1 has the table array fromulas built down 25000 and accross about 12 columns. The table is about 500,000 rows of 12 colums. It was running ok, very sluggish) I have 2.888 G processor on the computer. But I stuck in a =if(countif( formula to check for duplicates. Now this sheets is running so slow it is almost unuseabel. any sujjestions |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Super slow table array formulas
Yes, you really should be using a proper database.
But if you want to use Excel you should sort your table array so that you can use VLOOKUP(lookupvalue,TableArray,Column,True) See http://www.decisionmodels.com/optspeede.htm for examples of how to do this when you may have missing values. This will probably be about 10000 times faster. Splitting the table array into a separate file will be slower, if it works at all. Charles ___________________________________ London Excel Users Conference April 1-2 The Excel Calculation Site http://www.decisionmodels.com "Dylan @ UAFC" wrote in message ... sure I can send. Its a huge file I could email. The basics is, The data is driven off a unique phone #. Wich is the table array. It had to done because we really have to many sloppy data entry and no speed. So I wanted it to popluate the Address, city state zip county, name ect. 1) do you think the speed would be higher If i had the table array as a seperate file and not just on sheet2, 500,000 row and need to built to add additional data 2) I have the formul set to =IF(ISNA(VLOOKUP(G4,Sheet1!$A$1:$J$500000,3,FALSE) ),"",VLOOKUP(G4,Sheet1!$A$1:$J$500000,3,FALSE)) because I hate those #n/A's I did notice this was a tremdous accomplishmen for the computer to copy down, wich took 25 min. The next issue I was having was duplicate data entry wich I then used the formula =IF(COUNTIF($G$4:$G$25000,G6)1,"FALSE"," TRUE") I needed it to render Treu is in fact the data was clean and new and then render False is the appointment was in fact a dup. If you could show me how this formula would leave the cell empty until data is typed in the corrisoponding cell, that would great. I have tried a # of way/ In sheet one there is another table array formula hidden in sheet one, but the range is literally r4:s12. that could not be putting to much strain. Any suggestions. "Don Guillett" wrote: Can you break up your ranges into smaller groups. I had to correct one for a client recently where he was using a VLookup for 65000 rows. If desired, I will take a look if you send to my address below. -- Don Guillett Microsoft MVP Excel SalesAid Software "Dylan @ UAFC" wrote in message ... I have a data entry sheet were sheet 1 has the table array fromulas built down 25000 and accross about 12 columns. The table is about 500,000 rows of 12 colums. It was running ok, very sluggish) I have 2.888 G processor on the computer. But I stuck in a =if(countif( formula to check for duplicates. Now this sheets is running so slow it is almost unuseabel. any sujjestions |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Super slow table array formulas
I will check out the site and see what I can do.
Are you saying that excel just might not do thejob, an d we should look into a more web data base driven system Like a serrious of SQL codes or something??? "Charles Williams" wrote: Yes, you really should be using a proper database. But if you want to use Excel you should sort your table array so that you can use VLOOKUP(lookupvalue,TableArray,Column,True) See http://www.decisionmodels.com/optspeede.htm for examples of how to do this when you may have missing values. This will probably be about 10000 times faster. Splitting the table array into a separate file will be slower, if it works at all. Charles ___________________________________ London Excel Users Conference April 1-2 The Excel Calculation Site http://www.decisionmodels.com "Dylan @ UAFC" wrote in message ... sure I can send. Its a huge file I could email. The basics is, The data is driven off a unique phone #. Wich is the table array. It had to done because we really have to many sloppy data entry and no speed. So I wanted it to popluate the Address, city state zip county, name ect. 1) do you think the speed would be higher If i had the table array as a seperate file and not just on sheet2, 500,000 row and need to built to add additional data 2) I have the formul set to =IF(ISNA(VLOOKUP(G4,Sheet1!$A$1:$J$500000,3,FALSE) ),"",VLOOKUP(G4,Sheet1!$A$1:$J$500000,3,FALSE)) because I hate those #n/A's I did notice this was a tremdous accomplishmen for the computer to copy down, wich took 25 min. The next issue I was having was duplicate data entry wich I then used the formula =IF(COUNTIF($G$4:$G$25000,G6)1,"FALSE"," TRUE") I needed it to render Treu is in fact the data was clean and new and then render False is the appointment was in fact a dup. If you could show me how this formula would leave the cell empty until data is typed in the corrisoponding cell, that would great. I have tried a # of way/ In sheet one there is another table array formula hidden in sheet one, but the range is literally r4:s12. that could not be putting to much strain. Any suggestions. "Don Guillett" wrote: Can you break up your ranges into smaller groups. I had to correct one for a client recently where he was using a VLookup for 65000 rows. If desired, I will take a look if you send to my address below. -- Don Guillett Microsoft MVP Excel SalesAid Software "Dylan @ UAFC" wrote in message ... I have a data entry sheet were sheet 1 has the table array fromulas built down 25000 and accross about 12 columns. The table is about 500,000 rows of 12 colums. It was running ok, very sluggish) I have 2.888 G processor on the computer. But I stuck in a =if(countif( formula to check for duplicates. Now this sheets is running so slow it is almost unuseabel. any sujjestions |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Super slow table array formulas
Also Charles, the biggest perfomacne issue I had was
When I incerted the =if(countif( fromula to check for duplicate data entry, only in a one column range e1:e25000. but this made a huge perfmance ajustment. any suggestions there "Charles Williams" wrote: Yes, you really should be using a proper database. But if you want to use Excel you should sort your table array so that you can use VLOOKUP(lookupvalue,TableArray,Column,True) See http://www.decisionmodels.com/optspeede.htm for examples of how to do this when you may have missing values. This will probably be about 10000 times faster. Splitting the table array into a separate file will be slower, if it works at all. Charles ___________________________________ London Excel Users Conference April 1-2 The Excel Calculation Site http://www.decisionmodels.com "Dylan @ UAFC" wrote in message ... sure I can send. Its a huge file I could email. The basics is, The data is driven off a unique phone #. Wich is the table array. It had to done because we really have to many sloppy data entry and no speed. So I wanted it to popluate the Address, city state zip county, name ect. 1) do you think the speed would be higher If i had the table array as a seperate file and not just on sheet2, 500,000 row and need to built to add additional data 2) I have the formul set to =IF(ISNA(VLOOKUP(G4,Sheet1!$A$1:$J$500000,3,FALSE) ),"",VLOOKUP(G4,Sheet1!$A$1:$J$500000,3,FALSE)) because I hate those #n/A's I did notice this was a tremdous accomplishmen for the computer to copy down, wich took 25 min. The next issue I was having was duplicate data entry wich I then used the formula =IF(COUNTIF($G$4:$G$25000,G6)1,"FALSE"," TRUE") I needed it to render Treu is in fact the data was clean and new and then render False is the appointment was in fact a dup. If you could show me how this formula would leave the cell empty until data is typed in the corrisoponding cell, that would great. I have tried a # of way/ In sheet one there is another table array formula hidden in sheet one, but the range is literally r4:s12. that could not be putting to much strain. Any suggestions. "Don Guillett" wrote: Can you break up your ranges into smaller groups. I had to correct one for a client recently where he was using a VLookup for 65000 rows. If desired, I will take a look if you send to my address below. -- Don Guillett Microsoft MVP Excel SalesAid Software "Dylan @ UAFC" wrote in message ... I have a data entry sheet were sheet 1 has the table array fromulas built down 25000 and accross about 12 columns. The table is about 500,000 rows of 12 colums. It was running ok, very sluggish) I have 2.888 G processor on the computer. But I stuck in a =if(countif( formula to check for duplicates. Now this sheets is running so slow it is almost unuseabel. any sujjestions |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Super slow table array formulas
If your data (assume in column A) is sorted it is very simple and fast to
check for duplicates like this =if(a2=a1,"Dup","") and copy down. Charles ___________________________________ London Excel Users Conference April 1-2 The Excel Calculation Site http://www.decisionmodels.com "Dylan @ UAFC" wrote in message ... Also Charles, the biggest perfomacne issue I had was When I incerted the =if(countif( fromula to check for duplicate data entry, only in a one column range e1:e25000. but this made a huge perfmance ajustment. any suggestions there "Charles Williams" wrote: Yes, you really should be using a proper database. But if you want to use Excel you should sort your table array so that you can use VLOOKUP(lookupvalue,TableArray,Column,True) See http://www.decisionmodels.com/optspeede.htm for examples of how to do this when you may have missing values. This will probably be about 10000 times faster. Splitting the table array into a separate file will be slower, if it works at all. Charles ___________________________________ London Excel Users Conference April 1-2 The Excel Calculation Site http://www.decisionmodels.com "Dylan @ UAFC" wrote in message ... sure I can send. Its a huge file I could email. The basics is, The data is driven off a unique phone #. Wich is the table array. It had to done because we really have to many sloppy data entry and no speed. So I wanted it to popluate the Address, city state zip county, name ect. 1) do you think the speed would be higher If i had the table array as a seperate file and not just on sheet2, 500,000 row and need to built to add additional data 2) I have the formul set to =IF(ISNA(VLOOKUP(G4,Sheet1!$A$1:$J$500000,3,FALSE) ),"",VLOOKUP(G4,Sheet1!$A$1:$J$500000,3,FALSE)) because I hate those #n/A's I did notice this was a tremdous accomplishmen for the computer to copy down, wich took 25 min. The next issue I was having was duplicate data entry wich I then used the formula =IF(COUNTIF($G$4:$G$25000,G6)1,"FALSE"," TRUE") I needed it to render Treu is in fact the data was clean and new and then render False is the appointment was in fact a dup. If you could show me how this formula would leave the cell empty until data is typed in the corrisoponding cell, that would great. I have tried a # of way/ In sheet one there is another table array formula hidden in sheet one, but the range is literally r4:s12. that could not be putting to much strain. Any suggestions. "Don Guillett" wrote: Can you break up your ranges into smaller groups. I had to correct one for a client recently where he was using a VLookup for 65000 rows. If desired, I will take a look if you send to my address below. -- Don Guillett Microsoft MVP Excel SalesAid Software "Dylan @ UAFC" wrote in message ... I have a data entry sheet were sheet 1 has the table array fromulas built down 25000 and accross about 12 columns. The table is about 500,000 rows of 12 colums. It was running ok, very sluggish) I have 2.888 G processor on the computer. But I stuck in a =if(countif( formula to check for duplicates. Now this sheets is running so slow it is almost unuseabel. any sujjestions |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Super & Subscript | Excel Discussion (Misc queries) | |||
| Excel super slow start, answers? | Excel Discussion (Misc queries) | |||
| pivot table from recordset - very slow performance | Charts and Charting in Excel | |||
| Sumproduct formulas & slow response | Excel Worksheet Functions | |||
| Excel formulas are slow to update | Excel Worksheet Functions | |||
![Dylan @ UAFC[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
