|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 15th, 2009, 07:37 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
how to count unique values and show each different one
I have a long column of ZIPS. I need a formula that will tell me which ZIPS
I have and how many there are of each. 
|
|
#2
May 15th, 2009, 07:47 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to count unique values and show each different one
Suppose these are in A1:A1000
Insert a new row 1 and in A1 type a label (could be just the word ZIP) Select all of the occupied A cells; use Data | Advanced Filter and specify Unique Now you have a list of the zips; copy these to A1 of Sheet 2 Select the data in A of Sheet1 again and use Data | Advanced Filter and specify show all In B1 of Sheet 2 use =COUNTIF(Sheet1!A:A,Sheet2!A1) Copy this don the column OR : take the plunge and learn the magic of Pivot Tables Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx best wsihes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "mmwrites" wrote in message ... I have a long column of ZIPS. I need a formula that will tell me which ZIPS I have and how many there are of each.
|
|
#3
May 15th, 2009, 07:50 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to count unique values and show each different one
If you have them in Col A
Then enter a heading in A1 (like Zip, insert a row if required), select Col A choose Data-Filter-Advanced Filter Click on Unique values only enter B1 in Copy to This will give you list of unique zip in Col B now in C2 enter =COUNTIF(A:A,B2) and copy down till the end of your data to get the count "mmwrites" wrote: I have a long column of ZIPS. I need a formula that will tell me which ZIPS I have and how many there are of each.
|
|
#4
May 15th, 2009, 07:53 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to count unique values and show each different one
Use a Pivot Table. It will produce a nice listing of each unique code with
the number of times it occurs. http://peltiertech.com/Excel/Pivots/pivottables.htm -- Gary''s Student - gsnu200853 "mmwrites" wrote: I have a long column of ZIPS. I need a formula that will tell me which ZIPS I have and how many there are of each.
|
|
#5
May 16th, 2009, 12:11 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to count unique values and show each different one
Hello,
I suggest to take Pfreq or Lfreq from he http://www.sulprobil.com/html/listfreq.html Regards, Bernd
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
