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

find unique records
 

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

find unique records
 

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

find unique records
 

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.
Paste this formula in cell B2 and copy down.
This will list those unique items in alphabetical order without duplicates.