Where We Have Been So Far:

More on how the =vlookup function is useful. Examples end at 2:30

This exercise will involve downloading, cleaning and comparing FBI crime data from 2010-2014

You will use a new command, VLOOKUP, to compare the cities in the two years sop you have an accurate comparison
—=vlookup is “find and replace” on steroids
You will use Text to Columns as a way to match disparate data sources

Step 1: Download FBI UCR Crime Data, one year, all Arkansas localities and all categories, 2014 and 2010
https://www.ucrdatatool.gov/Search/Crime/Local/OneYearofData.cfm

Step 2: Combine the 2010 data in the same workbook as the 2014 Data
Create Data Biography
Save as an Xlsx workbook

Step 3: Four Corners Test on the 2010-2014 data.
Do they match? How are they different?

Step 4: Fill in missing population data on 2014 Crime Data
Video: =vlookup and text to columns to insert population data

–Copy population data, 2014 Census Figures, into a new tab from this table:
PEP_2014_PEPANNRES
–Filter population to blanks in 2014 Crime data

A) Make sure the names match.
TEXT TO COLUMNS: Split the names out in columns.
DATA: TEXT TO COLUMNS. How many columns? Column per word. Create four new columns
Clean up Fort Smith
—DEFINE MATERIAL TO SPLIT UP
—DATA | TEXT TO COLUMNS
—DELIMITED then select SPACE
—Replace contents with selected cells
**–Label the new columns: Agency, Agency1, Agency2
**—-Clean up stray blank columns

Do the same with the Census:
Label Geography1, Geography2

Conclude =vlookup

Compare 2014-2010 Crime in Arkansas