Counting unique values in a table
 

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

Counting unique values in a table
 

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)


"BRob" wrote in message
...
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

Counting unique values in a table
 

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(r ng,G$1:G1))+1),"")

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


--
Biff
Microsoft Excel MVP


"Ron Coderre" wrote in message
...
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)


"BRob" wrote in message
...
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

Counting unique values in a table
 

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)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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(r ng,G$1:G1))+1),"")

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


--
Biff
Microsoft Excel MVP


"Ron Coderre" wrote in message
...
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)


"BRob" wrote in message
...
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

Counting unique values in a table
 

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)



"T. Valko" wrote in message
...
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)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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(r ng,G$1:G1))+1),"")

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


--
Biff
Microsoft Excel MVP


"Ron Coderre" wrote in message
...
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)


"BRob" wrote in message
...
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

Counting unique values in a table
 

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...

"BRob" wrote:

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

Counting unique values in a table
 

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?


--
Biff
Microsoft Excel MVP


"Ron Coderre" wrote in message
...
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)



"T. Valko" wrote in message
...
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)


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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(r ng,G$1:G1))+1),"")

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


--
Biff
Microsoft Excel MVP


"Ron Coderre" wrote in message
...
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)


"BRob" wrote in message
...
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