Results 1 to 9 of 9
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I have a table with about 500 records, each consisting of three short fields: City, State, Year. Each record represents an event that was held in a certain location in a certain year. There are only 50 possible States, of course. Within any state, a few cities are common destinations, but any city can have an event. (In other words, a new city can appear on the list at any time.)

    I want to get a report that gives me counts of the number of events held in each state and each city. Year isn't important right now.

    I'm familiar with the COUNTA function, but NOT familiar with pivot tables. I think my solution might lie in creating a pivot table, but I'm not sure, and I don't even know where to begin.

    Who can steer me in the right direction?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The following description is for Excel 2003 or before; it's slightly different in Excel 2007 and later because the menu bar has been replaced with the ribbon.

    - Your data table should have the field names (City etc.) in its first row.
    - Click in any cell in the data table, for examply on the field name City.
    - Select Data | Pivot Table and Pivot Chart Report.
    - Click Next>.
    - Excel should automatically select the entire data table; if not you can do that now. Then click Next>.
    - Click Layout...
    - Drag State to the Row area.
    - Also drag City to the Row area.
    - Drag City to the Data area; the button should read Count of City.
    - Click OK.
    - Specify whether the pivot table should be created on a new sheet or on an existing sheet (if the latter, specify the cell to be used as upper left corner).
    - Click Finish.

    Here are some links to pivot table tutorials:
    PivotTable reports 101 (for Excel 2002/2003)
    Excel Pivot Tables Tutorial (for Excel 2007)

  3. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    This is great... I WILL check out the tutorials and some books that I have. (BTW, I'm using Excel 2003)

    In the meantime, what I have is totals for each city, but not totals for each state. Also, my data has some events in Bloomington, IL and Bloomington, MN. They are separate places, and I want each Bloomington to be included in its own state.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  4. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    This system isn't allowing me to edit. Sorry.

    Regarding my previous post, I have figured out how to do it. Thanks.

    Now I'm off to the tutorials. ;-)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You should be able to edit your own posts; this is what I see at the bottom of my own posts (if they're not the first post in a thread):

    [attachment=87318:x.png]

    Clicking 'Edit' takes me to a simple editor where I can only edit the text and apply limited formatting; clicking 'Switch to Full Editor' in the simple editor activates the full editor where I can add/remove attachments etc.
    Attached Images Attached Images
    • File Type: png x.png (2.7 KB, 1 views)

  6. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    It doesn't seem to work. When I click the Edit icon, I go to a 'Fast Reply' screen. My post is not on it.

    I'm there now, and typing this. We'll see what happens when I click 'Post'.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If clicking 'Edit' takes you to 'Fast Reply', it must be a bug - you should report it in the [s]Lounge Matters[/s] oh pardon [s]Suggestion Box[/s] oops Feedback to Admins forum.

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    If clicking 'Edit' takes you to 'Fast Reply', it must be a bug - you should report it in the [s]Lounge Matters[/s] oh pardon [s]Suggestion Box[/s] oops Feedback to Admins forum.
    But be quick, just incase....

  9. #9
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I just reported it. Thanks for the info.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •