Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF calculates slow (10000+ records)
Hi,
We have a printer repair log where we count the number of times the printer has been in based on the serial number. But we have 1000+ log entries and every time we change a serial number or adds a new it calculates a long time all the way. Can this be speeded up or as an alternative only calculated the first time? A B C D 1 SerialScan Count Serial# 2 12345 4 12345 3 12 345 4 12345 4 12-345 4 12345 5 12-346 1 12346 6 12-347 1 12347 This is the formula in the Count column copied all the way down =IF(D3="";"";COUNTIF(D:D;D3)) Does not count if no serial (D3="") so at least empty rows do net get counted. The serial# is derived from SerialScan wich can contain serial with minus and spaces but stipped for counting purposes =(SUBSTITUTE(SUBSTITUTE(B3;" ";"");"-";"")) Any tips? -- br, Vidar |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF calculates slow (10000+ records)
Getting rid of the SUBSTITUTE() formulas in column D may help some - although
you may want to leave one or 2 of them near the end of the existing list as a reminder/seed for added entries later: Select the cells in column D that you want to convert to their current displayed value and use Edit -- Copy. Without taking any further action (leave those cells selected) use Edit -- Paste Special with the "Values" option selected. That will remove the formula, replacing it with what's displayed in the cell. That should reduce the recalculation load some. Keep in mind that after doing this, if you sort the data, you need to include all columns in the sort (column D will no longer rebuild those cell contents from column A entries). "VidarHe" wrote: Hi, We have a printer repair log where we count the number of times the printer has been in based on the serial number. But we have 1000+ log entries and every time we change a serial number or adds a new it calculates a long time all the way. Can this be speeded up or as an alternative only calculated the first time? A B C D 1 SerialScan Count Serial# 2 12345 4 12345 3 12 345 4 12345 4 12-345 4 12345 5 12-346 1 12346 6 12-347 1 12347 This is the formula in the Count column copied all the way down =IF(D3="";"";COUNTIF(D:D;D3)) Does not count if no serial (D3="") so at least empty rows do net get counted. The serial# is derived from SerialScan wich can contain serial with minus and spaces but stipped for counting purposes =(SUBSTITUTE(SUBSTITUTE(B3;" ";"");"-";"")) Any tips? -- br, Vidar |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF calculates slow (10000+ records)
Hello Vidar,
I suggest to take my UDF Lfreq or Pfreq: http://www.sulprobil.com/html/listfreq.html Regards, Bernd |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF calculates slow (10000+ records)
Hi
Tried to Remove the SUBSTITUTE formula in column D (serial#) but this have no effect. It is the COUNTIF formula on 1000 records counting in 10000 serial numbers that takes up alle the calculating time. But we have removed the COUNTIF formula on previous years on the C column (count). This have an impact now but then we can only see how many times the printer was repaired up to this date. But better than nothing. -- br Vidar "JLatham" wrote: Getting rid of the SUBSTITUTE() formulas in column D may help some - although you may want to leave one or 2 of them near the end of the existing list as a reminder/seed for added entries later: Select the cells in column D that you want to convert to their current displayed value and use Edit -- Copy. Without taking any further action (leave those cells selected) use Edit -- Paste Special with the "Values" option selected. That will remove the formula, replacing it with what's displayed in the cell. That should reduce the recalculation load some. Keep in mind that after doing this, if you sort the data, you need to include all columns in the sort (column D will no longer rebuild those cell contents from column A entries). "VidarHe" wrote: Hi, We have a printer repair log where we count the number of times the printer has been in based on the serial number. But we have 1000+ log entries and every time we change a serial number or adds a new it calculates a long time all the way. Can this be speeded up or as an alternative only calculated the first time? A B C D 1 SerialScan Count Serial# 2 12345 4 12345 3 12 345 4 12345 4 12-345 4 12345 5 12-346 1 12346 6 12-347 1 12347 This is the formula in the Count column copied all the way down =IF(D3="";"";COUNTIF(D:D;D3)) Does not count if no serial (D3="") so at least empty rows do net get counted. The serial# is derived from SerialScan wich can contain serial with minus and spaces but stipped for counting purposes =(SUBSTITUTE(SUBSTITUTE(B3;" ";"");"-";"")) Any tips? -- br, Vidar |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF calculates slow (10000+ records)
Was trying to avoid macros with the new xlsm and trusting ... We will keep
the workaround with removing the formulas on records from previous years and only keep valus there. But then another workaround could be to just run COUNTIF the first time the record was entered. The most pressing is to get notified that the printer has been in fore and then we can manually search for the occurences/filter. Any ideas on how to do this? -- br Vidar "Bernd P" wrote: Hello Vidar, I suggest to take my UDF Lfreq or Pfreq: http://www.sulprobil.com/html/listfreq.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to expand the autofilter range 1000 to 10000? | Excel Worksheet Functions | |||
How do I add time abouve 10000 hours in excel? | Excel Worksheet Functions | |||
take out comma ID, 10000 to ID 100000 in column | Excel Discussion (Misc queries) | |||
divide values by 10000 | New Users to Excel | |||
When I type in 100 a 1 appears, 10000 and 100 appear | Excel Discussion (Misc queries) |