Extracting List of Occurrences

[Terry Bennett]

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.

 

[Gaurav]

Data | Filter | Advanced Filter

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

is that what you want?

 

[Pete_UK]

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

 

[T. Valko]

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 Data>Filter>Advanced 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

 

[JP]

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:A10000,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

 

[Terry Bennett]

Many thanks guys - much appreciated!

Terry

Data | Filter | Advanced Filter

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

is that what you want?

 

[Ron Rosenfeld]

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

 

[Terry Bennett]

Thanks Ron

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