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

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" <del**eteto-hash#(E-Mail Removed)> wrote in message
news:9oQIj.31761$(E-Mail Removed)...
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

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)


--
Biff
Microsoft Excel MVP


"Ron Coderre" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <del**eteto-hash#(E-Mail Removed)> wrote in message
> news:9oQIj.31761$(E-Mail Removed)...
> 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)
>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Ron Coderre" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <del**eteto-hash#(E-Mail Removed)> wrote in message
>> news:9oQIj.31761$(E-Mail Removed)...
>> 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
>>
>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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)
>>
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Ron Coderre" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <del**eteto-hash#(E-Mail Removed)> wrote in message
>>> news:9oQIj.31761$(E-Mail Removed)...
>>> 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
>>>
>>>
>>>
>>
>>
>
>

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

>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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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)
>>>
>>>
>>> --
>>> Biff
>>> Microsoft Excel MVP
>>>
>>>
>>> "Ron Coderre" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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" <del**eteto-hash#(E-Mail Removed)> wrote in message
>>>> news:9oQIj.31761$(E-Mail Removed)...
>>>> 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
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
>