Filtering

• Once again you can use the movies dataset.
• Last time we sorted the data.
• Start by sorting the data based on
  • Date : oldest to newest
  • Park : A to Z
• Click anywhere in the document and select Filter from the Data tab in the Sort & Filter command group.
  • 
  • This will place a Filter Handle on each of the headers.
• Clicking the Filter button again will
  • remove the filter.
  • And any filters that have been applied
• Using the filter is fairly self explanatory but
  • How many PG-13 movies
  • 
  • were shown on a Monday?
  • 
  • Sorted by Date shown
  • 
• You can remove filters.
• Notice with the date filter, you can drill down to specific days.
  • 
• Can you find the moves shown between July 1 and July 5?
  • Use the Date Filters selection
  • 
  • Select Between
  • And select the proper dates
  • 
• Notice that there are special filters for
  • Numbers (wards)
  • Text (park)
• A sort of helpful but tricky last item
  • Turn filtering off.
  • Select column G
  • Select Advance on the Sort & Filter command group of the Data tab.
  • 
  • This will bring up the Advanced Filter tool.
    • 
    • Select Copy to another location
    • Use the location selector in Copy To to select a location to copy the data to.
    • Click on Unique records only
    • 
• This is a minimalistic approach to this tool.
  • Reference
  • Let's say I want a copy of all records for
    • Shown in wards larger than 10 but less than 15
    • Or in ward 23
    • Before I start I want to set up a "Criteria Range"
      • Insert four blank rows before the start of the data.
        • Right click on row 1 and select insert
        • Repeat until you get this:
        • 
      • Add the following Headings
        • 
      • Since we want an and (ward > 10 and ward < 15)
        • Put >10 in cell A2
        • Put < 15 in cell B2
      • Since we want an or ((ward > 10 and word < 15) or ward = 23)
        • Put 23 in cell A3
      • Select
        • The table (a5:o215) as the List range
        • Criteria (a1:c3) as the Criteria range
        • Copy to another location
        • Select somewhere else to copy it to.
        • 
      • To filter on the ratings
        • use: ="=PG-13"
      • Every new column is an "and"
      • Every new row is a "or"
      • Operators
        • < , <=
        • >, >=
        • <>
        • =
    • What does the following do?
      • Selects all movies from ward 23
      • Also (or) selects all movies from wards between 10 and 15 (exclusive) with a rating of PG-13 (and of all three)
      • Also (or) selects movies from ward 26 and a rating of PG
      • Also (or) selects movies from ward 26 and a rating of NR
  • Can you produce a list of movies that
    • Were shown on a Tuesday (Tues)
    • In Community Areas 37 through 68 (inclusive)
    • With a rating of NR or PG