In this lab, you will: Required: 1. Create a column titled Customer_St in the Sales_Transactions table. Use VLOOKUP to match the Customer_ID from the Sales_Transaction table to the Customer_ID in the Customers table, and pull in the data from the column containing Customer_St. 2. Create a column titled Customer_City. Use VLOOKUP to match the Customer_ID from the Sales_Transactions table to the Customer_ID in the Customers table, and pull in the data from the column containing Customer_St in the Sales_Transactions. For a challenge, create both VLOOKUPs without using your mouse, just use your keyboard to reference column and table names! 3. Return to the PivotTable you created, and click Refresh from the Analyze tab on the ribbon to view the two new attributes in the field list, Customer_St and Customer_City. Ask the Question: How can we pull in descriptive attributes from separate tables into the transaction table that we wish to use for analysis? Master the Data: Continue working with the same data file (Lab 4-3 Data). Add in two new columns, one for Customer_St and another for Customer_City. Software needed Excel Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4) Data: Excel File Lab 4-3 Data.xlsx. Perform the Analysis: Refer to Lab 4-3 Alternate in the text for instructions and Lab 4-3 steps for each the of lab parts. Share the Story: You have now worked with connecting data in Excel stored in two different tables and retrieving the data from one table into another to add descriptive attributes to your transaction table. 1. Click into the formula of the first cell of the Customer_City column you added to the Sales_Transactions table, and take a screenshot of your work. Label it "Lab 4-3 ALT Submission 1. 2. Click into the formula of the first cell of the Customer_St column you added to the Sales_Transactions table, and take a screenshot of your work. Label it "Lab 4-3 ALT Submission 2. 3. Take a screenshot of the PivotTable field list that contains the two new variables. Label it Lab 4-3 ALT Submission 3." Sales_Order_Quantity_Sold Product_Sale Price 12 105 8 5 85 105 120 6 Product_Code 2004 2005 2004 2002 2001 2004 2004 2003 2004 5 2 95 105 105 8 11 3 100 105 100 2003 7 3 100 95 1 8 7 1 Sales Order_ID 2 20062 3 20168 4 20383 5 20564 6 20140 7 20310 8 20308 9 20308 10 20009 11 20233 12 20163 13 20224 14 20199 15 20291 16 20451 17 20451 18 20012 19 20494 20 20494 21 20537 22 20250 23 20251 24 20003 25 20241 26 20253 27 20005 28 20005 29 20090 30 20033 31 20211 32 20211 33 20338 2003 2001 2005 2005 2006 2002 2005 2005 2004 2004 2005 85 85 90 120 9 2 85 1 6 2 85 105 6 105 2004 5 11 85 105 90 105 Sales_Order_Date Sales_Employee_ID 11/22/19 1007 12/27/19 1007 3/4/20 1007 4/28/20 1007 12/20/19 1006 2/11/20 1007 2/11/20 1006 2/11/20 1006 11/2/19 1006 1/16/20 1006 12/25/19 1006 1/13/20 1006 1/7/20 1007 2/4/20 1007 3/24/20 1006 3/24/20 1006 11/4/19 1006 4/8/20 1006 4/8/20 1006 4/19/20 1007 1/22/20 1006 1/22/20 1007 11/1/19 1006 1/19/20 1007 1/22/20 1007 11/1/19 1006 11/1/19 1006 12/1/19 1006 11/12/19 1007 1/10/20 1007 1/10/20 1007 2/22/20 1006 3/7/20 1007 12/4/19 1007 3/14/20 1006 4/20/20 1006 1/29/20 1006 3/10/20 1007 12/25/19 1007 1/14/20 1006 3/3/20 1006 11/21/19 1006 11/29/19 1007 2/10/20 1007 2/10/20 1007 2/10/20 1007 2/12/20 1006 11/12/19 1006 1/13/20 1006 1/15/20 1006 1/27/20 1006 Customer_ID 2001 2001 2001 2001 2002 2002 2004 2004 2005 2006 2007 2007 2008 2008 2008 2008 2009 2009 2009 2009 2010 2010 2011 2011 2011 2012 2012 2012 2013 2013 2013 2013 2013 2014 2016 2016 2017 2017 2019 2019 2020 2021 2021 2021 2021 2021 2021 3022 2022 2022 2022 2006 2004 2005 2006 2001 2002 8 2 10 9 85 90 8 95 120 100 90 10 1 12 12 2003 6 85 85 100 9 9 34 20393 35 20097 36 20418 37 20543 38 20277 39 20406 40 20161 12 3 3 1 4 12 100 90 100 90 85 90 2006 2005 2005 2003 2003 2006 2003 3006 2005 2006 2001 2001 2003 2001 2005 2001 2004 2003 2002 2001 2002 2004 41 20229 42 20379 43 20056 44 20080 9 12 8 95 95 100 95 85 11 3 4 6 95 105 100 4. 45 20305 46 20305 47 20305 48 20312 49 20031 50 20223 51 20230 52 20266 4 1 120 95 120 105 6 5 Sales Order_ID Sales_Order_Date Sales_Employee_ID Customer_ID Product_Code Sales_Order_Quantity_Sold Product_Sale_Price H 4 1 1 1 105 100 105 100 90 1 3 6 100 95 10 4 9 9 2 105 105 85 95 90 90 4 11 8 105 10 85 105 100 5 6 11 4 105 100 10 7 120 95 105 5 2 11 7 53 20452 54 20454 55 20265 56 20404 57 20404 58 20471 59 20513 60 20218 61 20307 62 20307 63 20227 64 20438 65 20375 66 20465 67 20120 68 20496 69 20020 70 20024 71 20063 72 20183 73 20183 74 20256 75 20053 76 20086 77 20202 78 20179 79 20453 80 20495 81 20040 82 20098 83 20283 84 20328 85 20384 86 20013 87 20013 88 20089 89 20055 90 20440 91 20516 92 20280 93 20280 94 20378 95 20403 96 20416 97 20138 98 20142 99 20559 100 20017 101 20043 102 20200 103 20200 3/25/20 1007 3/25/20 1006 1/27/20 1006 3/9/20 100 3/9/20 1006 3/29/20 1007 4/13/20 1007 1/12/20 1007 2/11/20 1007 2/11/20 1007 1/14/20 1006 3/18/20 1007 3/2/20 1007 3/28/20 1006 12/12/19 1006 4/8/20 1006 11/7/19 1006 11/8/19 1007 11/22/19 1006 1/1/20 1006 1/1/20 1006 1/24/20 1006 11/20/19 1006 11/30/19 1006 1/8/20 1006 12/30/19 1006 3/25/20 1007 4/8/20 1007 11/14/19 1007 12/4/19 1007 1/31/20 1007 2/17/20 1007 3/4/20 1007 11/4/19 1007 11/4/19 1007 12/1/19 1007 11/20/19 1007 3/19/20 1007 4/13/20 1006 1/30/20 1006 1/30/20 1006 3/3/20 1007 3/9/20 1006 3/13/20 1006 12/19/19 1007 12/20/19 1007 4/25/20 1007 11/7/19 1006 11/16/19 1006 1/7/20 1007 1/7/20 1007 2022 2022 2023 2024 2024 2025 2025 2026 2027 2027 2028 2028 2029 3029 2031 2031 2032 2033 2034 2034 2034 2034 2035 2035 2035 2036 2036 2036 2037 2037 2037 2037 2037 2038 2038 2038 2039 2039 2039 2040 2040 2040 2040 2040 2041 2042 2042 2043 2043 2043 2043 2004 2003 2004 2003 2006 2003 2001 2004 2004 2005 2001 2006 2006 2004 2005 2004 2003 2004 2003 2002 2001 2004 2004 2005 2005 2004 2006 2005 2003 2003 2006 2006 2003 2003 2002 2005 2004 2001 2002 2006 2001 2001 2004 2003 2006 2004 2004 2001 2005 2003 2004 105 85 85 105 90 6 3 4 2 2 4 85 100 100 90 90 4 11 10 2 100 100 120 85 4 10 11 3 11 105 95 120 4 90 7 7 95 95 4 2 4 105 100 90 105 105 95 9 11 1 12 12 10 85 100 105 Sales_Order_Quantity_Sold Product_Sale_Price H 1 J TU Customer_ID LU 2043 2043 Product Code LUVA 2004 2003 105 100 105 2044 2044 2045 2045 2004 2006 2005 2003 2 1 1 9 12 8 6 2046 2004 2003 2003 2004 2 9 9 11 1 6 6 90 85 100 105 100 100 105 105 85 100 105 2004 2005 9 11 2003 2004 2001 8 8 2002 7 2003 2 10 2004 2003 95 120 100 105 100 85 85 105 6 2005 2005 12 2 2004 9 Sales_Order_ID 10 2009 105 20461 106 20545 107 20407 100 20407 109 20357 110 20535 111 20314 112 20450 113 20368 114 20106 115 20126 116 20126 117 20217 118 20151 119 20518 120 20282 121 20285 122 20424 123 20460 124 20262 125 20023 126 20068 127 20249 128 20539 129 20037 130 20037 131 20001 132 20137 133 20155 134 20439 135 20261 136 20492 137 20093 138 20325 139 20124 140 20059 141 20103 142 20271 143 20491 144 20532 145 20192 146 20242 147 20458 148 20441 149 20503 150 20503 151 152 Sales_Order_Date Sales_Employee_ID ucuzu UuU 3/27/20 1007 4/21/20 1007 3/10/20 1006 3/10/20 1006 2/27/201006 4/19/20 1007 2/13/20 1007 3/24/20 1007 2/29/20 1007 12/7/19 1006 12/16/19 1006 12/16/19 1006 1/12/20 1007 12/22/19 1007 4/14/20 1007 1/31/20 1006 2/1/20 1007 3/16/20 1006 3/27/20 1006 1/26/20 1007 11/8/19 1007 11/24/19 1007 1/22/20 1006 4/19/20 1006 11/13/19 1007 11/13/19 1007 11/1/19 1006 12/18/19 1006 12/23/19 1006 3/19/20 1007 1/25/20 1006 4/8/20 1006 12/2/19 1007 2/15/20 1006 12/15/19 1007 11/22/19 1006 12/5/19 1006 1/28/20 1007 4/7/20 1006 4/17/20 1007 1/6/20 1007 1/19/20 1006 3/26/20 1007 3/19/20 1007 4/10/20 1006 4/10/20 1006 8 8 2046 2047 2048 2048 2048 2048 2049 2049 2050 2050 2050 2050 2051 2052 2052 2053 2053 2054 2054 2056 2056 2056 2056 2057 2057 2058 2058 2059 2060 2060 2060 2060 2060 2061 2061 2061 2062 2062 2062 2004 2004 2004 2005 2001 2003 10 3 3 10 105 105 105 85 95 100 100 100 10 2003 100 2003 2003 2003 2002 2003 2004 1 6 10 1 7 11 6 6 7 11 3 11 12 3 100 120 100 105 85 100 2005 2003 2005 2003 2003 85 100 100 105 105 100 2004 2004 2003 2003 2005 2004 11 10 6 100 B5 105 2 In this lab, you will: Required: 1. Create a column titled Customer_St in the Sales_Transactions table. Use VLOOKUP to match the Customer_ID from the Sales_Transaction table to the Customer_ID in the Customers table, and pull in the data from the column containing Customer_St. 2. Create a column titled Customer_City. Use VLOOKUP to match the Customer_ID from the Sales_Transactions table to the Customer_ID in the Customers table, and pull in the data from the column containing Customer_St in the Sales_Transactions. For a challenge, create both VLOOKUPs without using your mouse, just use your keyboard to reference column and table names! 3. Return to the PivotTable you created, and click Refresh from the Analyze tab on the ribbon to view the two new attributes in the field list, Customer_St and Customer_City. Ask the Question: How can we pull in descriptive attributes from separate tables into the transaction table that we wish to use for analysis? Master the Data: Continue working with the same data file (Lab 4-3 Data). Add in two new columns, one for Customer_St and another for Customer_City. Software needed Excel Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4) Data: Excel File Lab 4-3 Data.xlsx. Perform the Analysis: Refer to Lab 4-3 Alternate in the text for instructions and Lab 4-3 steps for each the of lab parts. Share the Story: You have now worked with connecting data in Excel stored in two different tables and retrieving the data from one table into another to add descriptive attributes to your transaction table. 1. Click into the formula of the first cell of the Customer_City column you added to the Sales_Transactions table, and take a screenshot of your work. Label it "Lab 4-3 ALT Submission 1. 2. Click into the formula of the first cell of the Customer_St column you added to the Sales_Transactions table, and take a screenshot of your work. Label it "Lab 4-3 ALT Submission 2. 3. Take a screenshot of the PivotTable field list that contains the two new variables. Label it Lab 4-3 ALT Submission 3." Sales_Order_Quantity_Sold Product_Sale Price 12 105 8 5 85 105 120 6 Product_Code 2004 2005 2004 2002 2001 2004 2004 2003 2004 5 2 95 105 105 8 11 3 100 105 100 2003 7 3 100 95 1 8 7 1 Sales Order_ID 2 20062 3 20168 4 20383 5 20564 6 20140 7 20310 8 20308 9 20308 10 20009 11 20233 12 20163 13 20224 14 20199 15 20291 16 20451 17 20451 18 20012 19 20494 20 20494 21 20537 22 20250 23 20251 24 20003 25 20241 26 20253 27 20005 28 20005 29 20090 30 20033 31 20211 32 20211 33 20338 2003 2001 2005 2005 2006 2002 2005 2005 2004 2004 2005 85 85 90 120 9 2 85 1 6 2 85 105 6 105 2004 5 11 85 105 90 105 Sales_Order_Date Sales_Employee_ID 11/22/19 1007 12/27/19 1007 3/4/20 1007 4/28/20 1007 12/20/19 1006 2/11/20 1007 2/11/20 1006 2/11/20 1006 11/2/19 1006 1/16/20 1006 12/25/19 1006 1/13/20 1006 1/7/20 1007 2/4/20 1007 3/24/20 1006 3/24/20 1006 11/4/19 1006 4/8/20 1006 4/8/20 1006 4/19/20 1007 1/22/20 1006 1/22/20 1007 11/1/19 1006 1/19/20 1007 1/22/20 1007 11/1/19 1006 11/1/19 1006 12/1/19 1006 11/12/19 1007 1/10/20 1007 1/10/20 1007 2/22/20 1006 3/7/20 1007 12/4/19 1007 3/14/20 1006 4/20/20 1006 1/29/20 1006 3/10/20 1007 12/25/19 1007 1/14/20 1006 3/3/20 1006 11/21/19 1006 11/29/19 1007 2/10/20 1007 2/10/20 1007 2/10/20 1007 2/12/20 1006 11/12/19 1006 1/13/20 1006 1/15/20 1006 1/27/20 1006 Customer_ID 2001 2001 2001 2001 2002 2002 2004 2004 2005 2006 2007 2007 2008 2008 2008 2008 2009 2009 2009 2009 2010 2010 2011 2011 2011 2012 2012 2012 2013 2013 2013 2013 2013 2014 2016 2016 2017 2017 2019 2019 2020 2021 2021 2021 2021 2021 2021 3022 2022 2022 2022 2006 2004 2005 2006 2001 2002 8 2 10 9 85 90 8 95 120 100 90 10 1 12 12 2003 6 85 85 100 9 9 34 20393 35 20097 36 20418 37 20543 38 20277 39 20406 40 20161 12 3 3 1 4 12 100 90 100 90 85 90 2006 2005 2005 2003 2003 2006 2003 3006 2005 2006 2001 2001 2003 2001 2005 2001 2004 2003 2002 2001 2002 2004 41 20229 42 20379 43 20056 44 20080 9 12 8 95 95 100 95 85 11 3 4 6 95 105 100 4. 45 20305 46 20305 47 20305 48 20312 49 20031 50 20223 51 20230 52 20266 4 1 120 95 120 105 6 5 Sales Order_ID Sales_Order_Date Sales_Employee_ID Customer_ID Product_Code Sales_Order_Quantity_Sold Product_Sale_Price H 4 1 1 1 105 100 105 100 90 1 3 6 100 95 10 4 9 9 2 105 105 85 95 90 90 4 11 8 105 10 85 105 100 5 6 11 4 105 100 10 7 120 95 105 5 2 11 7 53 20452 54 20454 55 20265 56 20404 57 20404 58 20471 59 20513 60 20218 61 20307 62 20307 63 20227 64 20438 65 20375 66 20465 67 20120 68 20496 69 20020 70 20024 71 20063 72 20183 73 20183 74 20256 75 20053 76 20086 77 20202 78 20179 79 20453 80 20495 81 20040 82 20098 83 20283 84 20328 85 20384 86 20013 87 20013 88 20089 89 20055 90 20440 91 20516 92 20280 93 20280 94 20378 95 20403 96 20416 97 20138 98 20142 99 20559 100 20017 101 20043 102 20200 103 20200 3/25/20 1007 3/25/20 1006 1/27/20 1006 3/9/20 100 3/9/20 1006 3/29/20 1007 4/13/20 1007 1/12/20 1007 2/11/20 1007 2/11/20 1007 1/14/20 1006 3/18/20 1007 3/2/20 1007 3/28/20 1006 12/12/19 1006 4/8/20 1006 11/7/19 1006 11/8/19 1007 11/22/19 1006 1/1/20 1006 1/1/20 1006 1/24/20 1006 11/20/19 1006 11/30/19 1006 1/8/20 1006 12/30/19 1006 3/25/20 1007 4/8/20 1007 11/14/19 1007 12/4/19 1007 1/31/20 1007 2/17/20 1007 3/4/20 1007 11/4/19 1007 11/4/19 1007 12/1/19 1007 11/20/19 1007 3/19/20 1007 4/13/20 1006 1/30/20 1006 1/30/20 1006 3/3/20 1007 3/9/20 1006 3/13/20 1006 12/19/19 1007 12/20/19 1007 4/25/20 1007 11/7/19 1006 11/16/19 1006 1/7/20 1007 1/7/20 1007 2022 2022 2023 2024 2024 2025 2025 2026 2027 2027 2028 2028 2029 3029 2031 2031 2032 2033 2034 2034 2034 2034 2035 2035 2035 2036 2036 2036 2037 2037 2037 2037 2037 2038 2038 2038 2039 2039 2039 2040 2040 2040 2040 2040 2041 2042 2042 2043 2043 2043 2043 2004 2003 2004 2003 2006 2003 2001 2004 2004 2005 2001 2006 2006 2004 2005 2004 2003 2004 2003 2002 2001 2004 2004 2005 2005 2004 2006 2005 2003 2003 2006 2006 2003 2003 2002 2005 2004 2001 2002 2006 2001 2001 2004 2003 2006 2004 2004 2001 2005 2003 2004 105 85 85 105 90 6 3 4 2 2 4 85 100 100 90 90 4 11 10 2 100 100 120 85 4 10 11 3 11 105 95 120 4 90 7 7 95 95 4 2 4 105 100 90 105 105 95 9 11 1 12 12 10 85 100 105 Sales_Order_Quantity_Sold Product_Sale_Price H 1 J TU Customer_ID LU 2043 2043 Product Code LUVA 2004 2003 105 100 105 2044 2044 2045 2045 2004 2006 2005 2003 2 1 1 9 12 8 6 2046 2004 2003 2003 2004 2 9 9 11 1 6 6 90 85 100 105 100 100 105 105 85 100 105 2004 2005 9 11 2003 2004 2001 8 8 2002 7 2003 2 10 2004 2003 95 120 100 105 100 85 85 105 6 2005 2005 12 2 2004 9 Sales_Order_ID 10 2009 105 20461 106 20545 107 20407 100 20407 109 20357 110 20535 111 20314 112 20450 113 20368 114 20106 115 20126 116 20126 117 20217 118 20151 119 20518 120 20282 121 20285 122 20424 123 20460 124 20262 125 20023 126 20068 127 20249 128 20539 129 20037 130 20037 131 20001 132 20137 133 20155 134 20439 135 20261 136 20492 137 20093 138 20325 139 20124 140 20059 141 20103 142 20271 143 20491 144 20532 145 20192 146 20242 147 20458 148 20441 149 20503 150 20503 151 152 Sales_Order_Date Sales_Employee_ID ucuzu UuU 3/27/20 1007 4/21/20 1007 3/10/20 1006 3/10/20 1006 2/27/201006 4/19/20 1007 2/13/20 1007 3/24/20 1007 2/29/20 1007 12/7/19 1006 12/16/19 1006 12/16/19 1006 1/12/20 1007 12/22/19 1007 4/14/20 1007 1/31/20 1006 2/1/20 1007 3/16/20 1006 3/27/20 1006 1/26/20 1007 11/8/19 1007 11/24/19 1007 1/22/20 1006 4/19/20 1006 11/13/19 1007 11/13/19 1007 11/1/19 1006 12/18/19 1006 12/23/19 1006 3/19/20 1007 1/25/20 1006 4/8/20 1006 12/2/19 1007 2/15/20 1006 12/15/19 1007 11/22/19 1006 12/5/19 1006 1/28/20 1007 4/7/20 1006 4/17/20 1007 1/6/20 1007 1/19/20 1006 3/26/20 1007 3/19/20 1007 4/10/20 1006 4/10/20 1006 8 8 2046 2047 2048 2048 2048 2048 2049 2049 2050 2050 2050 2050 2051 2052 2052 2053 2053 2054 2054 2056 2056 2056 2056 2057 2057 2058 2058 2059 2060 2060 2060 2060 2060 2061 2061 2061 2062 2062 2062 2004 2004 2004 2005 2001 2003 10 3 3 10 105 105 105 85 95 100 100 100 10 2003 100 2003 2003 2003 2002 2003 2004 1 6 10 1 7 11 6 6 7 11 3 11 12 3 100 120 100 105 85 100 2005 2003 2005 2003 2003 85 100 100 105 105 100 2004 2004 2003 2003 2005 2004 11 10 6 100 B5 105 2
