In class exercises

Part I - Smelter File

1. Save As New File

2. Copy, paste special to remove formatting

3. Delete unecessary columns (commodity)

4. Rename headers

5. Assign countries to all locations - look out for records missing info (ctrl a, c, x, v, z, y)

6. Split location columns (text to columns on comma and parens to split name and province, and local name)

7. Copy and paste to get city, province, and location into separate columns

8. Do TRIM on city and province to remove trailing and leading spaces

9. Do FIND and REPLACE to remove parens from local name

10. For furance footnote demo left, right, mid, find

=LEFT(K6,3) -returns 3 chars from left: Rev 
=RIGHT(K6,3) - returns 3 chars from right: (S)
=FIND("(",K6) - returns position with first parens: 15 
=LEFT(K6,FIND("(",K6)) - returns all chars from left up to 15th position at parens: Reverberatory (
=RIGHT(K6,FIND("(",K6)) - returns all chars from right up to 15th position at parens: verberatory (
=MID(K6,15,20) - from the 15th position grab all chars 20 spaces to left: (S)
=MID(K6,FIND("(",K6),20) - find the parens, and from that position grab all chars 20 spaces to left: (S) 
=IFERROR(MID(K7,FIND("(",K7),20),"") - it error, return nothing, otherwise do the formula

11. Assign country codes to names using VLOOKUP, do the rest manually

=VLOOKUP(B2,Sheet2.A1:C249,3,0)

In order to work, the ID column in the other sheet must be in the first column position
=VLOOKUP(value in current sheet to look up, array in other sheet to search through, column number in other sheet that has the value you want to retrieve, false to get an exact match)

12. Assign unique IDs

13. Column Order

Part II - Fix Trade File

1. Save as new file

2. Eliminate unecessary columns (table code, commodity, commodity code, quanity, q code)

3. Rename columns

4. Delete summary rows (EU)

5. VLOOKUP to get iso country code from their table

6. Convert flow code, weights and dollars from text to values - be careful when copying and pasting down

7. Concatenate year, country code, and export code to create unique id

=CONCATENATE(B2,"_",D2, "_", E2)

8. Example of pivot table to summarize by year - sort and select only 2015, countries as rows, year and flow as cols, weights and dollars as values

Part III - Fix Mine File - Porcu (poryphyry copper)

1. Save as new table

2. Eliminate uneeded columns (agemy, depage, empldepth, dep5km10km, dep5km, depid, latdeg, latmin, latsec, londeg, lonmin, lonsec, rockdep, rockmap, rockmapdep, discdate, startdate, ore_a, ore_b, alter_a, alter_b, sulf_a, sulf_b, sulf_area)

3. Assign country codes with vlookup =VLOOKUP(C2,Sheet3.$A$1:$C$249,3,0)