+ Reply to Thread
Results 1 to 6 of 6

Grouping Similar Records

  1. #1
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Grouping Similar Records

    I recently queried a large dataset from Access. It is sales data for auto parts and is segmented into 3 category tiers. 1st is Department (e.g. Air Fresheners). 2nd is Category (e.g. Spray Air Fresheners). 3rd is Subcategory (e.g. Aerosol Spray Air).

    I want to be able to sum sales based on Department, and then be able to expand into the 2nd and 3rd tiers by clicking a button. And, of course be able to go back to the 1st tier.

    How can I tell Excel which 2nd tier items to associate with 1st tier and so on?

    Pivot table possibly? I have never used one.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Grouping Similar Records

    It would help a lot if you uploaded a sample workbook with some typical data.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Grouping Similar Records

    Sure, here it is in '07 format.

    Maybe I need to look at the Subtotals feature?
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Grouping Similar Records

    Also, I will be making separate Tabs for Cash, Charge, and Total values (unless there is a way to group those similarly...), and I will be summing each by week. I was thinking I could incorporate the Sumif function into however it is best to group them.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Grouping Similar Records

    I think you're probably going to be better using a Pivot Table.

    Just click somewhere in the table and select Insert | Pivot Table on the ribbon.

    Put Dept, Category and Sub-Category as the Row Labels.

    The things you want to sum will go in the data pane.

    You could put the Year and Week headings in the Report Filter pane.

    Regards

  6. #6
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Grouping Similar Records

    Thanks, I will give it a shot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1