+ Reply to Thread
Results 1 to 5 of 5

Can't figure out how to filter a range of data with sub-headers - NEED HELP!

  1. #1
    Registered User
    Join Date
    08-11-2016
    Location
    Boston
    MS-Off Ver
    2016
    Posts
    3

    Can't figure out how to filter a range of data with sub-headers - NEED HELP!

    Screen Shot 2016-08-11 at 1.59.32 PM.png

    I have a range of data that shows the number of certain types of vehicles sold by color, pear year, per week. Column A is the year, Column B is the week, Row 1 is the type of vehicle, Row 2 is the color of the vehicle.

    I need to be able to filter the table to display:

    -all of a certain week
    -all of a certain year
    -all of a certain vehicle
    -all of a certain color
    -a combination of the above

    For example I'd like to be able to filter it down so I can view only the red vehicles sold in week 2 of each year, or the trucks sold per week in 2016. Ideally there would be some form of drop down list.

    Is there any way of doing this?

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Can't figure out how to filter a range of data with sub-headers - NEED HELP!

    I would strongly recommend NOT using merged cells in your data. they make things MUCH more difficult than they are worth, especially IN a dataset.

    Then I would suggest you take the CARS/TRUCKS/BOATS/POGO STICKS and make a column to identify TYPE (or whatever you want to call it). this will make the dataset 4 times as long but a fourth as wide (since you are condensing down to 3 measure columns.

    Then you can VERY easily identify everything with very simple pivot tables or even a pivot chart. You could take it one step further and create column for COLOR, to make it a clean and tidy standardized dataset, which allows the maximum flexibility with your reporting.


    If you make those changes and post a sample workbook I can show you how awesome pivot tables are
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    08-11-2016
    Location
    Boston
    MS-Off Ver
    2016
    Posts
    3

    Re: Can't figure out how to filter a range of data with sub-headers - NEED HELP!

    Hi mikeTRON,

    I can't figure out what you mean by making a column for TYPE and COLOR. I've attached the spreadsheet, so if you wouldn't mind doing 1 or 2 for me and the I could do the rest.
    I really appreciate the help.

    Thanks!
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Can't figure out how to filter a range of data with sub-headers - NEED HELP!

    Sure, take a look at this.

    1 tab for a clean standardized dataset, then you can pivot and change the report with ease.

    Pivot table information:
    http://chandoo.org/wp/excel-pivot-tables/

    To filter it is fairly easy on the data tab, as each column has a filter now.
    For the pivot table, click in the dimension you want to filter, then click on the drop down in ROW labels or COLUMN labels, depending on if what you want to filter is in.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-11-2016
    Location
    Boston
    MS-Off Ver
    2016
    Posts
    3

    Re: Can't figure out how to filter a range of data with sub-headers - NEED HELP!

    Great! Thank you so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. autofilter method of range class failed -header has tables headers and non table headers
    By naveen.acheanz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-16-2016, 08:21 AM
  2. [SOLVED] How to post a range - headers and data?
    By mlcb in forum Suggestions for Improvement
    Replies: 242
    Last Post: 04-14-2016, 07:31 PM
  3. Question about How to post a range - headers and data
    By FlameRetired in forum Excel General
    Replies: 1
    Last Post: 10-26-2015, 09:15 PM
  4. Replies: 1
    Last Post: 05-20-2015, 06:21 AM
  5. [SOLVED] How to Copy a filter data with headers, excluding the headers?
    By Elomaldo in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-16-2014, 03:35 PM
  6. Replies: 4
    Last Post: 05-12-2014, 06:58 PM
  7. Advanced Filter : One Criteria Cell Breaks the Filter - Returns Only Headers
    By PaulGW in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2014, 10:06 AM

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