convert a list to count by county

T

Takeadoe

From what I'm hearing, they say Excel is limited only by one's
imagination. I can't imagine how to do this, so perhaps someone would
be willing to help me!

I've got a list of 700 county names. How hard would it be to convert
that list to a count by county. In other words, when all is said and
done, I will know how many entries I have for each county. In the past,
I've used SAS - I'm trying to reduce my dependence on this software and
learn Excel!

Mike
 
B

Bernard Liengme

I will assume the names are in A1:A700
Two methods:
1) Simplest but more typing
Type a list of the counties in D1:D40 (or as needed)
In E1 enter =COUNTIF($A$1:$A$700,E1)
Copy this down to the last county name
Copying is easily done by double clicking the fill handle - small black
square in E1's lower right corner
2) Pivot Table - need some experimenting
Tutorials available
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://lacher.com/toc/tutpiv.htm
Right click on the row header 1 (the 1 to the far left of the first row)
Use the Insert command
In A1 enter the word County
Select A1:A701; use command Data: Pivot Table
Step 1 - you are using an Excel List, click Next
Step 2 - you have selected A1:A7001; click Next
Step 3 - click Existing Worksheet and type E1 in box
Click Finish
Drag the County token to the left column Row Field; drag County token to the
large space called Item Field
Done
Worth learning even if you need to play for a while.
Pivot tables are not dynamic - if you change a name right click pivot table
and use Update
best wishes
 
T

Takeadoe

Bernard,

Thank you for the pointers. You noted that, "Pivot tables are not
dynamic", which caught my attention. Perhaps you saw my post that
followed this - where I was asking for help on how to update a Pivot
table with the current year's data - in this case, add another column
to my table. Is this something that can easily be done with a named
range and the offset function? I found this example
(http://contextures.com/xlPivot01.html) which leads me to believe that
there is a way to easily add data to an existing pivot table without
having to recreate the thing each year. Any help would be much
appreciated.

Mike
 
Top