Stupid Excel Question

Kevy Baby · 03-24-2007, 02:05 PM

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:

Select "Auto Filter"
In the drop down selector (proper name unknown), select the first region, say for example, "ABCD"
Go to the County column and select the first available county. Write down the name of the county and the quantity.
Select the name of the second county, write down the name and quantity.
Continue this until all counties in that region are accounted for
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?

BarTopDancer · 03-24-2007, 02:06 PM

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

Not Afraid · 03-24-2007, 02:17 PM

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.

Ghoulish Delight · 03-24-2007, 03:14 PM

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)".

Kevy Baby · 03-24-2007, 05:55 PM

Quote:

Originally Posted by Ghoulish Delight

I presume the list of counties doesn't change month to month?

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.

Kevy Baby · 03-24-2007, 05:56 PM

Quote:

Originally Posted by BarTopDancer

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

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

Sub la Goon · 03-24-2007, 06:54 PM

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!

Alex · 03-24-2007, 11:57 PM

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

Kevy Baby · 03-25-2007, 08:20 PM

Quote:

Originally Posted by Sub la Goon

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!

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.

03-24-2007, 02:05 PM	#1
Kevy Baby Chowder Head Join Date: Jan 2005 Location: Yes Posts: 18,500	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: Select "Auto Filter" In the drop down selector (proper name unknown), select the first region, say for example, "ABCD" Go to the County column and select the first available county. Write down the name of the county and the quantity. Select the name of the second county, write down the name and quantity. Continue this until all counties in that region are accounted for 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? __________________ The thing about quotes on the internet is that you cannot verify their validity. - Abraham Lincoln
	Submit to Quotes

03-24-2007, 02:06 PM	#2
BarTopDancer Prepping... Join Date: Jan 2005 Location: Here, there, everywhere Posts: 11,405	Aw man. I thought you would have had an answer by now or I would have posted for you. I'm sorry! __________________ Spork is the new MacGyver Postcards From My Edge
	Submit to Quotes

03-24-2007, 02:17 PM	#3
Not Afraid HI! Join Date: Jan 2005 Posts: 17,108	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.
	Submit to Quotes

03-24-2007, 03:14 PM	#4
Ghoulish Delight I Floop the Pig Join Date: Jan 2005 Location: Alternative Swankstyle Posts: 19,348	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)". __________________ 'He who receives an idea from me, receives instruction himself without lessening mine; as he who lights his taper at mine, receives light without darkening me.' -TJ
	Submit to Quotes

03-24-2007, 06:54 PM	#7
Sub la Goon Ride me! Join Date: Jul 2006 Location: The line forms here... Posts: 326	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! __________________ You roll the dice, you move your mice.
	Submit to Quotes

03-24-2007, 11:57 PM	#8
Alex . Join Date: Feb 2005 Posts: 13,354	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).
	Submit to Quotes