 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / August 2007Mailing List Questions
I've got a rather long mailing list in Excel and am not familiar with all the calculation features in Excel. Hoping someone can help me with this. One column is zip codes (5-digit). I would like to know how to make a list showing the number of entries in each zip code. Basically that's the question. I'm doing it the old fashion way now. I upload a file at alt.binaries.crafts.pictures to show what I'm asking about. If you can't get access to it, post what binary group you can, and I'll post it there also. (Actually 3 files - different formats so anyone can view them). Thanks! Have a look in the help index for COUNTIF  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com I've got a rather long mailing list in Excel and am not familiar with all the calculation features in Excel. Hoping someone can help me with this. One column is zip codes (5-digit). I would like to know how to make a list showing the number of entries in each zip code. Basically that's the question. I'm doing it the old fashion way now. I upload a file at alt.binaries.crafts.pictures to show what I'm asking about. If you can't get access to it, post what binary group you can, and I'll post it there also. (Actually 3 files - different formats so anyone can view them). Thanks! Thanks, Don. I'm probably too much of a beginner and don't even understand the help page. Maybe an example might be easier for me to understand. This is a simple -- mine has hundreds of numbers. List A below is a list of numbers. List B is what I'm looking for. List A 1 1 1 2 3 4 4 6 6 6 6 6 List B (lists how many of each item are in the list) 1 3 2 1 3 1 4 2 6 5 > Have a look in the help index for COUNTIF > [quoted text clipped - 11 lines] > > Thanks! in c2=countif(a2:a15,b2) copied down with the fill handle 1 1 3 1 2 1 1 3 1 2 4 2 3 6 5 4 4 6 6 6 6 6 SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com Thanks, Don. I'm probably too much of a beginner and don't even understand the help page. Maybe an example might be easier for me to understand. This is a simple -- mine has hundreds of numbers. List A below is a list of numbers. List B is what I'm looking for. List A 1 1 1 2 3 4 4 6 6 6 6 6 List B (lists how many of each item are in the list) 1 3 2 1 3 1 4 2 6 5 > Have a look in the help index for COUNTIF > [quoted text clipped - 13 lines] > > Thanks! Thanks a million, Don! That's exactly what I was hoping for. Is there a formula to go thru the numbers in A and list the unique ones in B? Bob > in c2=countif(a2:a15,b2) copied down with the fill handle > 1 1 3 [quoted text clipped - 57 lines] > > > > Thanks! If you don't mind me jumping in: In B1 enter, =A1 In B2, enter this *array* formula: =IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0)),"",INDEX(IF(ISBLANK($A$ 1:$A$200),"",$A$1:$A$200),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0))) SignatureArray formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of the regular <Enter>, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. *After* the CSE entry, copy down until you get #N/A errors. This tells you that you have exhausted the unique numbers in Column A. I have sized the formula to A200. You can revise that as necessary. As you add additional values to Column A, those errors will change to display additional uniques. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- Thanks a million, Don! That's exactly what I was hoping for. Is there a formula to go thru the numbers in A and list the unique ones in B? Bob > in c2=countif(a2:a15,b2) copied down with the fill handle > 1 1 3 [quoted text clipped - 43 lines] > news:uS7tsX15HHA.1484@TK2MSFTNGP06.phx.gbl... > > Have a look in the help index for COUNTIF Thanks, RD! Exactly what I needed. An example is the best thing for me. I can use that now and learn the COUNTIF, etc. Can you recommend a good study book or online 'course' where I can learn these type of operations? Thanks again to all of you! Bob > If you don't mind me jumping in: > [quoted text clipped - 76 lines] > > > > > > Thanks! Appreciate the feed-back. As for your other question, check this link: http://tinyurl.com/2bah9v SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- Thanks, RD! Exactly what I needed. An example is the best thing for me. I can use that now and learn the COUNTIF, etc. Can you recommend a good study book or online 'course' where I can learn these type of operations? Thanks again to all of you! Bob > If you don't mind me jumping in: > > In B1 enter, > =A1 > > In B2, enter this *array* formula: =IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0)),"",INDEX(IF(ISBLANK($A$ > 1:$A$200),"",$A$1:$A$200),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0))) > [quoted text clipped - 67 lines] > > > > > > Thanks! I'd probably do this with a pivot table. http://www.cpearson.com/excel/pivots.htm > I've got a rather long mailing list in Excel and am not familiar with all the calculation features in Excel. Hoping someone can help me with this. > [quoted text clipped - 3 lines] > > Thanks! Thanks, Barb. I'm afraid that's still too advanced for me though. Bob > I'd probably do this with a pivot table. > [quoted text clipped - 7 lines] > > > > Thanks!
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.