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