Counting unique values in a table

[BRob]

I've got a 20 column 30 row table and in it are whole numbers. (Although not important their values are from about 100 to 850.)

What I want to do is create another worksheet with 2 columns :

a.. Col 1 - A sorted list (descending) of unique values in the table
b.. Col 2 - A count of the number of occurences of that value in the table.

Can SKS give me some idea of how I might go about it.

TIA

Rob

 

[Ron Coderre]

OK....This isn't pretty...but here's what I came up with:

With the data in A1:U30

Put is ARRAY FORMULA (in sections)
....committed with CTRL+SHIFT+ENTER
....(instead of just ENTER)
in

W1: =SMALL((COUNTIF($A$1:$U$30,ROW(INDEX(A:A,100):
INDEX(A:A,850)))>0)*ROW(INDEX(A:A,100):
INDEX(A:A,850)),ROW()+SUMPRODUCT(--(COUNTIF($A$1:
$U$30,ROW(INDEX(A:A,100):INDEX(A:A,850)))=0)))

Copy W1 into W2 and down until it returns an error.

The put this regular formula in
X1: =COUNTIF($A$1:$U$30,W1)

Copy that formula down as far as you need.

Perhaps the above will inspire somebody
to come up with something more elegant.
(I certainly hope so)
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


I've got a 20 column 30 row table and in it are whole numbers. (Although not
important their values are from about 100 to 850.)

What I want to do is create another worksheet with 2 columns :

a.. Col 1 - A sorted list (descending) of unique values in the table
b.. Col 2 - A count of the number of occurences of that value in the
table.

Can SKS give me some idea of how I might go about it.

TIA

Rob

 

[T. Valko]

Tested on a smaller range 5Cx10R = A1:E10

Requires that the formula not be entered on row 1. The cell above the first
formula cell must not be a number that might be in the table.

Entered this array formula** in G2 and copied down until it returns blanks:

=IF(ROWS(G$2:G2)<=COUNT(1/FREQUENCY(rng,rng)),LARGE(rng,SUMPRODUCT(COUNTIF(rng,G$1:G1))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

A couple of after thoughts...

Since the formula already requires array entry we can replace SUMPRODUCT
with SUM.

Also, if you can "live" without the error trap we can save some resources
and shorten the formula to:

=LARGE(rng,SUM(COUNTIF(rng,G$1:G1))+1)

 

[Ron Coderre]

Following classic reverse logic, first I build the awful
formula...THEN I check my formula stash and find a something
that seems to work! (Biff's "rng" ref reminded me to check)

With
A1:U30 containing numbers (or blanks)

This regular formula lists the unique numbers
in ascending order (with error checking):
W2:
=IF(ROWS($1:1)<=SUM(N(FREQUENCY($A$1:$U$30,
$A$1:$U$30)>0)),SMALL($A$1:$U$30,SUM(INDEX(
COUNTIF($A$1:$U$30,W$1:W1),0))+1),"")

This shorter regular formula does the same thing, but
returns #NUM! when it runs out of numbers:
W2:
=SMALL($A$1:$U$30,SUM(INDEX(COUNTIF($A$1:$U$30,W$1:W1),0))+1)

Either way, copy the formula down as far as needed.

To count the instances of each number:
X2: =IF(N(W2),COUNTIF($A$1:$U$30,W2),"")

I hope that helps.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Lori]

here's a simple method using a PivotTable:
http://www.freefilehosting.net/download/3eh39

to change the source data right-click, select the pivot table wizard and
then click Back...

 

[T. Valko]

I check my formula stash

(Biff's "rng" ref reminded me to check)

Whenever I post something that uses "rng" as a ref there's a good chance I
pulled it out of my stash!

Having a "stash" is very good! I say: why reinvent the wheel every day?