Homework 5: Things are Looking Up even More.

• Apply Index and Match to find values.
• Test results.

• It is from the PA Uniform Crime Reporting System
• It is hate crimes for all metropolitan areas in PA.
• I didn't save the date field.

• I would like you to practice index/match, not vlookup. Some of these tasks might be a bit silly, but please do them.
• In addition, I would like to format this data so it could easily be saved into a database.
• To do this, we will decompose the data into a series of tables.

The general plan is to break the data down into the following tables

• Crime : a table of all of the data, but with the following as indexes
• Motivation: the unique motivation types
• Statue : the unique statutes
• Gender : the unique genders
• Race : the unique races
• Religion : the unique religions
• Type : the unique victim types

This is probably not something you would normally do, decompose a table into final parts, but I feel the exercise is good. It will give you practice with the functions AND will help you understand what a dba wants.

Please do the following:

1. [10%] Enter your name in the About worksheet, maintain this worksheet
2. Remove the formatting from the raw data table.
   • Select the entire table.
   • On the Home tab, Editing workgroup select the Clear button
   • 
   • Select item number 2 on the drop down menu Clear Formats
3. [10%]Build a Gender table
   1. Start a new worksheet called Gender
   2. Extract the unique values for the gender for both column D and column I
      • You may do this with the advanced filter function on the data tab
      • Or use the function on page 441 of the book.
   3. Add Headers
   4. Sort Alphabetically
   5. In the end, the worksheet should contain
   6. 
4. [30 %] Repeat this for
   • Motivation
     • (Column A)
     • Sort alphabetically
     • Add headers
     • 
   • Statute
     • column B
     • Sort alphabetically
     • Add headers
     • 
   • Race
     • Extract both columns E and J
     • Combine and remove duplicates
     • Sort Alphabetically
     • Add headers
     • 
   • Religion
     • Extract both columns F and K
     • Combine and remove duplicates
     • Sort Alphabetically
     • Add headers
     • 
   • Type
     • column G
     • Sort alphabetically
     • Add headers
     • 
   • You will probably want to name all these columns for later use.
5. Make a copy of the RawData worksheet
   1. Name this Crimes
   2. Replace Column A, Motivation with the Motivation ID
      • Insert a column
      • Name it Motivation Key
      • Lookup the ID based on the motivation, look up the appropriate motivation id.
        • index/match please
      • 
      • Add a column to perform a lookup of the motivation based on the new Motivation Key
        • Index/Match please.
      • Add a column to test to see if the Motivation and the Check column are the same
        • Exact please.
      • 
      • Off to the right (column O right now)
        • Add a table, that checks to see if the check column is all true.
        • 
   3. Repeat this for the other tables.
      • 
   4. In the end, I ended up building a test table based on the column letter of the check
      • 
      • 
6. Make a Crimes table worksheet.
   1. Hide all but the valid data columns.
   2. Select the table
   3. Go to the Home tab in the Editing workgroup select Find & Select
   4. Pick Go To Special
   5. On the left hand side select Visible cells only, ok
   6. Copy the data
   7. Insert a new worksheet called CrimeData
   8. Paste as values
7. Save this workbook.

Submit