由买买提看人间百态

boards

本页内容为未名空间相应帖子的节选和存档,一周内的贴子最多显示50字,超过一周显示500字 访问原贴
Statistics版 - count unique values in file with 1 million rows
相关主题
请教EXCEL高手求助关于Excel IF 的function
问题:多个Excel的一个column 怎么能合并到一个里面?再问个傻问题, R的
SQL find distinct values in large table (转载)sas 疑问
[合集] 问个关于excel的土问题请教个VBA问题 (转载)
借人气问一个Excel sampling 问题 (转载)Urgent R Question
how to use Scan in SAS (so it works like parse in excel)R function to tabulate a matrix
问个r问题[合集] 请教一个简单的统计问题
excel function 求助_有包子请教一个proc transpose的问题
相关话题的讨论汇总
话题: excel话题: values话题: a5389579话题: unique话题: count
进入Statistics版参与讨论
1 (共1页)
l******9
发帖数: 579
1
I need to count the unique values in one column in EXCEL 2010.
The worksheet has 1 million rows and 10 columns. All cell values are string
or numbers.
I used the solution at Count unique values in a column in Excel
=SUMPRODUCT((A2:A1000000<>"")/COUNTIF(A2:A100000,A2:A1000000&""))
But, it runs so long time that the EXCEL is almost frozen. And, it generates
25 processes in Win 7.
Are there more efficient ways to do it ?
Also, in the column, all values have for format of
AX_Y
here, A is a character, X is an integer, Y is an integer from 1 to 10.
For example, A5389579_10
I need to cut off the part after (including) undersocre. for the example,
A5389579
This is what I need to count as unique values in all cells in one column.
For example, A5389579_10
A1543848_6
A5389579_8
here, the unique value has 2 after removing the part after underscore.
How to do it in EXCEL VBA and R (if no efficient solution for EXCEL) ?
Thanks !
1 (共1页)
进入Statistics版参与讨论
相关主题
请教一个proc transpose的问题借人气问一个Excel sampling 问题 (转载)
R data.framehow to use Scan in SAS (so it works like parse in excel)
A SAS Multiple Comparison Problem问个r问题
one quick question in SAS, thank you!!excel function 求助_有包子
请教EXCEL高手求助关于Excel IF 的function
问题:多个Excel的一个column 怎么能合并到一个里面?再问个傻问题, R的
SQL find distinct values in large table (转载)sas 疑问
[合集] 问个关于excel的土问题请教个VBA问题 (转载)
相关话题的讨论汇总
话题: excel话题: values话题: a5389579话题: unique话题: count