Improving Excel Performance - London Excel User Conference - July 19-21
Charles Williams wrote...
....
- comparison of 4 methods for doing a CountUnique: Array formula,
Sumproduct, UDF, additional formulae. (there is a speed improvement factor
of over 500 between the slowest and the fastest of these).
....
Won't be there, but I'm curios what the array formula would be. Is it
something like
=COUNT(1/(MATCH(Rng,Rng,0)=ROW($A$1:INDEX($A:$A,ROWS(Rng))) ))
or
=COUNT(1/FREQUENCY(Rng,Rng))
though the latter can only handle numeric values. You should consider
adding
=COUNTA(UNIQUEVALUES(Rng))
where UNIQUEVALUES is from Longre's MOREFUNC.xll add-in in order to
consider XLL add-in functions as well.
|