Mailing List Questions

  • Thread starter Thread starter Mail Man Bob
  • Start date Start date
M

Mail Man Bob

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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
 
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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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
 
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
 
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)))

--
Array 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
 
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
 
Appreciate the feed-back.

As for your other question, check this link:

http://tinyurl.com/2bah9v

--
Regards,

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