On Mon, 14 Jan 2008 22:14:01 -0000, "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.


You can download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/morefunc/english/

and use the UNIQUEVALUES function.

Something like:

=INDEX(UNIQUEVALUES(rng,1),ROWS($1:1))

will return the first unique value in "rng" (in alphabetical order). If you
fill down the formula as far as needed (until you get blanks returned), the
ROWS argument will increase by one and the function will return one each of
every entry.

Chip Pearson also has a VBA DistinctValues function described and documented at
http://www.cpearson.com/excel/distinctvalues.aspx which performs similarly.

This latter routine returns the results unsorted (in the order they appear in
rng) and will return an error (instead of a blank) when you drag down more than
the list of entries.
--ron