Here is an array-function solution, but with such a large range it was
painfully slow (XL 2003).

=SUM(IF(FREQUENCY(IF(LEN(A1:A10000)0,MATCH(A1:A10 000,A1:A10000,0),""),
IF(LEN(A1:A10000)0,MATCH(A1:A10000,A1:A10000,0)," "))0,1))

be sure to array-enter (Ctrl-Shift-Enter).


HTH,
JP

On Jan 14, 5:14*pm, "Terry Bennett" wrote:
I have a large data table. *In column A (say A1:A10000) there will be random
occurrences of particular items of text, ie:

Oranges
Apples
Pears
Grapes
Peaches
etc
etc

Obviously I can count how many times each item occurs using COUNTIF or
SUMPRODUCT but how do I extract a simple list of all the different types of
items? *I just need a list as above, ignoring the fact that any one item may
occur several hundred times (ie; a list exactly like you get on the
drop-down when filtering data).

Thanks in advance.