Ok so I have an excel file, that has 20,000 records/rows of data. There is a column called <STATE>, which could be PA, MD, CA, MT, etc.
I need to know how I can count how many times each state is populated and return the results to a new created sheet called <REPORT>. See example below.
State:
PA
PA
PA
MD
CA
MT
NJ
NJ
Report Results
PA 3
MD 1
CA 1
MT 1
NJ 2
Can this be done?
Last edited by Muhnamana; 11-11-2009 at 04:14 PM.
Try a Pivot Table..
http://peltiertech.com/Excel/Pivots/pivotstart.htm
Drag the State column to Row Area and Drag it again to Data Area...
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
List the state abbreviations in col A1:A50, and in B1 and copy down, =countif(state, A1), where state is the appropriate absolute range on the other sheet.
Entia non sunt multiplicanda sine necessitate
Sweet, this pivot table stuff works perfect.Originally Posted by NBVC
I'll look into this as well.
Thanks for the helps guys!
Actually, how can I go about programming this to automatically create the pivot table instead of having to create it each time?
You can right-click in the table and click Refresh Data.
Yeah I saw the refresh button but there are 4 different excel files which are supplied new to me each month. That's why I want hoping to automatic/program it so I can open the new excel file, click and button and boom, creates the pivot table, prints, emails, etc.
Hopefully someone with better VBA skills than I can help out...
...otherwise try posting the specific question automatically refreshing Pivot tables and the other things you want the button to do...
.. if you do that close this thread and mark solved.
Alright, thanks. I'll mark this as solved and post another question.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks