find unique records

[frankfurtjoe]

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

 

[frankfurtjoe]

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]

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

 

[RagDyeR]

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!
=====================================================


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

 

[GoBow777]

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: