find unique records

How do i search a column of data which contains for instance multiple numbers
of headings, find each unique record item (ie apple , pear, orange) and then
display a list of each type (no duplicates) in a new range of cells. I will
then be able to use this record list as a means of providing a "record type
count" in a different worksheet. Thanks

Thanks Jacob, but i am after is firt of all a list of the unique record items
so that i can then do a count seperately using that list asthe basis of the
count formula.

Cheers

"Jacob Skaria" wrote:

You can get the count of unique items using the below formula..

=SUMPRODUCT((A2:A100"")/COUNTIF(A2:A100,A2:A100&""))

If this post helps click Yes
---------------
Jacob Skaria


"frankfurtjoe" wrote:

How do i search a column of data which contains for instance multiple numbers
of headings, find each unique record item (ie apple , pear, orange) and then
display a list of each type (no duplicates) in a new range of cells. I will
then be able to use this record list as a means of providing a "record type
count" in a different worksheet. Thanks

You can get the count of unique items using the below formula..

=SUMPRODUCT((A2:A100"")/COUNTIF(A2:A100,A2:A100&""))

If this post helps click Yes
---------------
Jacob Skaria


"frankfurtjoe" wrote:

How do i search a column of data which contains for instance multiple numbers
of headings, find each unique record item (ie apple , pear, orange) and then
display a list of each type (no duplicates) in a new range of cells. I will
then be able to use this record list as a means of providing a "record type
count" in a different worksheet. Thanks

Say your original datalist is in A1 to A200.

In B1 enter:
=A1

In B2, enter this *array* formula:

=IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0 )),"",INDEX(IF(ISBLANK($A$1:$A$200),"",$A$1:$A$200 ),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0)))
--
Array formulas are entered using CSE, Ctrl Shift Enter, instead of the
regular Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* the array entry, copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"frankfurtjoe" wrote in message
...
Thanks Jacob, but i am after is firt of all a list of the unique record
items
so that i can then do a count seperately using that list asthe basis of the
count formula.

Cheers

"Jacob Skaria" wrote:

You can get the count of unique items using the below formula..

=SUMPRODUCT((A2:A100"")/COUNTIF(A2:A100,A2:A100&""))

If this post helps click Yes
---------------
Jacob Skaria


"frankfurtjoe" wrote:

How do i search a column of data which contains for instance multiple
numbers
of headings, find each unique record item (ie apple , pear, orange) and
then
display a list of each type (no duplicates) in a new range of cells. I
will
then be able to use this record list as a means of providing a "record
type
count" in a different worksheet. Thanks

Hello Frankfurtjoe:

Assuming your data starts in row 2, paste this formula in cell C2 and
copy down. Hide column C.

Code:
--------------------
=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,COUNTIF(A$2:A $1000,""&A2)+ROW()/100000,""))
--------------------


Paste this formula in cell B2 and copy down.

Code:
--------------------
=IF(ISERROR(SMALL(C:C,ROW()-1)),"",INDEX(A$2:A$1000,MID(SMALL(C:C,ROW()-1),FIND(".",SMALL(C:C,ROW()-1)),6)*100000-1,1))
--------------------


This will list those unique items in alphabetical order without
duplicates.




--
GoBow777