About Us

Terry Bennett

Thanks Ron

"Ron Rosenfeld" wrote in message
...
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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to determine the top 5 occurrences from a list of numbers?	Eric	Excel Discussion (Misc queries)	3	September 25th 07 10:30 AM
Extracting names from an unsorted list.	Brian H	Excel Worksheet Functions	7	March 3rd 07 09:42 PM
Extracting from a List	Mike Moore	Excel Discussion (Misc queries)	2	January 19th 07 08:37 PM
Extracting Data from a list	andrewc	Excel Worksheet Functions	3	December 13th 05 03:31 PM
Extracting Values on one list and not another	B Schwarz	Excel Discussion (Misc queries)	4	January 7th 05 01:48 PM

Menu

About Us