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

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

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

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

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