Extracting List of Occurrences
 

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.

Extracting List of Occurrences
 

Data | Filter | Advanced Filter

Check 'Copy to another location' and 'Unique Records Only'

is that what you want?


"Terry Bennett" wrote in message
...
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.

Extracting List of Occurrences
 

Insert a new worksheet, and then copy your data items from A1:A10000
into column A of the new sheet. If you do not have a header, then
insert a new row at the top and put some text in A1 to act as a
header. Then highlight from A1 down to the bottom of your data and
click on Data | Filter | Advanced Filter, and in the pop-up select
Unique Records Only, Copy to another location, and enter C1 in the
other location box. Click OK and you will find your list of unique
values (plus the header) in C1 onwards. Delete columns A and B.

Hope this helps.

Pete

On Jan 14, 10: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.

Extracting List of Occurrences
 

a list exactly like you get on the drop-down when filtering data).

Since you mentioned filter, use the advanced filter to extract the uniques
to another location.

Select the column of data (should have a column header or Excel will
complain).
Goto the menu DataFilterAdvanced filter
Copy to another location
List range: the source range of your data (should already be entered)
Copy to: cell reference where you want the new list to start
Unique records only
OK

--
Biff
Microsoft Excel MVP


"Terry Bennett" wrote in message
...
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.

Extracting List of Occurrences
 

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.

Extracting List of Occurrences
 

Many thanks guys - much appreciated!

Terry



"Gaurav" wrote in message
...
Data | Filter | Advanced Filter

Check 'Copy to another location' and 'Unique Records Only'

is that what you want?


"Terry Bennett" wrote in message
...
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.

Extracting List of Occurrences
 

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

Extracting List of Occurrences
 

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