Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 67
Default 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.


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 207
Default 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.




  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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.




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 897
Default 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.




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 67
Default 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.






  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 67
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 10:42 PM
Extracting from a List Mike Moore Excel Discussion (Misc queries) 2 January 19th 07 09:37 PM
Extracting Data from a list andrewc Excel Worksheet Functions 3 December 13th 05 04:31 PM
Extracting Values on one list and not another B Schwarz Excel Discussion (Misc queries) 4 January 7th 05 02:48 PM


All times are GMT +1. The time now is 05:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"