| 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Super slow table array formulas
not the case here.
we are dealing with super master spreedsheet that is distibuted out many way. The date the phoen entry occoured is important. However, no two phone entry can exist in the history of the data entry progect. The order is by date the intergerty is by phone # so unfor. the phoen # si really only random data that is not usefull in any calcution purpose "Charles Williams" wrote: 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)
Linear Mode
