Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Unique Values | Excel Discussion (Misc queries) | |||
Counting Unique Values | Excel Discussion (Misc queries) | |||
counting the # of unique values | Excel Worksheet Functions | |||
Counting Unique Values | Excel Worksheet Functions | |||
Counting unique values | Excel Worksheet Functions |