Extract Unique entries in a column
 

is there a function that would look at a range of cells and extract the
unique entries in the range and diplay them in a new "array" or column.

I would like to look at a column several hundered rows long and determine
what was entered in the column and how many times it was entered.

I know I could review the data and set up a column with the data I saw and
do a countif but I am looking for something a little cleaner that isn't
dependent on me to see every entry.

Thanks

Jeff
--
Jeff

Extract Unique entries in a column
 

One option would be to use a PivotTable. Another option would be to use
the Advanced Filter to create a unique list (select 'Unique records
only') and then use COUNTIF. Another option still would be to use a
formula to create your unique list and then use COUNTIF.

For a formula solution, assuming that A2:A10 contains your data, try the
following...

B1: leave empty

B2, copied down:

=IF(OR(COUNTIF($B$1:B1,A2:$A$10)=0),INDEX(A2:$A$10 ,MATCH(0,COUNTIF($B$1:B
1,A2:$A$10),0)),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"Jeff" wrote:

is there a function that would look at a range of cells and extract the
unique entries in the range and diplay them in a new "array" or column.

I would like to look at a column several hundered rows long and determine
what was entered in the column and how many times it was entered.

I know I could review the data and set up a column with the data I saw and
do a countif but I am looking for something a little cleaner that isn't
dependent on me to see every entry.

Thanks

Jeff

Extract Unique entries in a column
 

To extract unique entries, Advanced Filter will do that, see Debra
Dalgleish's site for step by step advice,
http://www.contextures.com/xladvfilter01.html
How many times an entry is entered I would think you will have to do
manually with a COUNTIF function,
Regards,
Alan.

"Jeff" wrote in message
...
is there a function that would look at a range of cells and extract the
unique entries in the range and diplay them in a new "array" or column.

I would like to look at a column several hundered rows long and determine
what was entered in the column and how many times it was entered.

I know I could review the data and set up a column with the data I saw and
do a countif but I am looking for something a little cleaner that isn't
dependent on me to see every entry.

Thanks

Jeff
--
Jeff

Extract Unique entries in a column
 

On Tue, 18 Oct 2005 11:01:01 -0700, "Jeff"
wrote:

is there a function that would look at a range of cells and extract the
unique entries in the range and diplay them in a new "array" or column.

I would like to look at a column several hundered rows long and determine
what was entered in the column and how many times it was entered.

I know I could review the data and set up a column with the data I saw and
do a countif but I am looking for something a little cleaner that isn't
dependent on me to see every entry.

Thanks

Jeff


=UNIQUEVALUES(rng) will return the results in a single column.

This function is part of Longre's free morefunc.xll add-in, available at
http://xcell05.free.fr/


--ron

Extract Unique entries in a column
 

Let column A from A3 on house the data with a label in A2.

In B1 enter: 0

In B2 enter: Idx

In B3 enter & copy down:

=IF(A3<"",IF(ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.9 9999999999999E+307,$B$1:B2)+1,""),"")

In D1 enter:

=LOOKUP(9.99999999999999E+307,B:B)

which also gives you a count of distinct/unique items.

In D2 enter: Distinct List

In D3 enter & copy down:

=IF(ROW()-ROW($D$3)+1<=$D$1,LOOKUP(ROW()-ROW($D$3)+1,$B$3:$B$65536,$A$3:$A$65536),"")

Another option is Advanced Filter, which is already suggested in this
thread.

Jeff wrote:
is there a function that would look at a range of cells and extract the
unique entries in the range and diplay them in a new "array" or column.

I would like to look at a column several hundered rows long and determine
what was entered in the column and how many times it was entered.

I know I could review the data and set up a column with the data I saw and
do a countif but I am looking for something a little cleaner that isn't
dependent on me to see every entry.

Thanks

Jeff