Stupid Excel Question
 

Here is one I cannot figure out.

I work with mailing lists frequently and often review them in Excel (I do not do any processing of the lists - I leave that to the programmers in their specialized mail programs - I just use the lists for reference). I have one mailing I do every month that I need to extract certain data out of. I know how to do it "manually", but there has to be a faster way.

Basically, I need to find out the count by counties in each of 16 regions within the list. How I do it now is:

1. Select "Auto Filter"
2. In the drop down selector (proper name unknown), select the first region, say for example, "ABCD"
3. Go to the County column and select the first available county. Write down the name of the county and the quantity.
4. Select the name of the second county, write down the name and quantity.
5. Continue this until all counties in that region are accounted for
6. Choose "Select All" under County, then select the next region and do steps 3-5 for that region. This is repeated until all sixteen regions and all records are counted.

I end up with a basic report that looks like this:

ABCD REGION
Alpha County: 23 records
Bravo County: 47 records
Charley County: 16 records
Delta County: 102 records

EFG REGION
Echo County: 77 records
Foxtrot County: 36 records

Obviously, this is tedious. I know there must be a simpler way to do this (especially since I actually have TWO lists each month I need to review this way!).

Any bright minds out there?

Aw man. I thought you would have had an answer by now or I would have posted for you. I'm sorry! :(

It's been ages since I've done this, but I believe there is something that enables you to sort and count by header. I used to do this with massive lists of products by company and I remember there being a function. After sorting and counting, you could colapse the information to only show the totals.

I presume the list of counties doesn't change month to month? If so, for each county, create a cell with "=countif(range, county name)". So, if the column with the county names is H, to count the number of entries for county Alpha, you'd put "=countif(H:H, Alpha)".

Ironically, the counties are not supposed to change. But that is what I am checking: to see if the list company did their job properly each month and included only the counties that they are supposed to.

And thank you for helping me out the other day. I appreciate it!

Two words: Pivot Table.

After you do one, you can save your pivot table program on a spreadsheet and copy that sheet into the next workbook with your data. Then right click the table, choose Pivot Table Wizard, and select your data range from the data sheet.

They can be tricky to start with but lots of fun!

Yes, pivot table is best. But if a solid answer hasn't come by Monday and I remember I'll post detailed instructions (I don't have Excel at home).

Never heard of a pivot table, but I'll look into it.

Thankfully, I don't need to do this for another couple of weeks.