count and display unique values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

joe - 18 Dec 2005 23:11 GMT

I need to count and display unique values

a
a
b
b
b
cc
cc
cc
cc
the is a formula to count the number of unique times a value exists but I
also want to display the values in a column.

Thnaks

Reply to this Message

Ron Coderre - 18 Dec 2005 23:44 GMT

Here's one easy ways (assuming you have a column heading):

Use a Pivot Table
Set ROW to your_col_heading
Set DATA to Count of your_col_heading

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

> I need to count and display unique values
>
[quoted text clipped - 11 lines]
>
> Thnaks

Reply to this Message

Bob Phillips - 19 Dec 2005 01:11 GMT

Cell B1: =A1
Cell B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$2
0&""),0)))

enter as an array formula, commit with Ctrl-Shift-Enter, then copy down

Cell C1: =IF(B1<>"",COUNTIF(A:A,B1),"")

and copy down

Signature

HTH

RP
(remove nothere from the email address if mailing direct)

> I need to count and display unique values
>
[quoted text clipped - 11 lines]
>
> Thnaks

Reply to this Message