Required information (The following information applies to the questions displayed below.) NOTE: Throughout this lab, every time a screenshot is requested, use your computer's screenshot tool, and paste each screenshot to the same Word document. Label each screenshot in accordance to what is noted in the lab. This document with all of the screenshots included should be uploaded through Connect as a Word or PDF document when you have reached the final step of the lab. 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. D E F H J K Product_Code 2004 Sales_Order_Quantity_Sold 12 2005 8 3 5 6 2004 2002 2001 2004 2004 2003 2004 5 2 8 11 3 3 7 3 3 1 8 2003 1 Sales Order_ID 2 20062 3 20168 4 5 20564 6 20140 6 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 34 20393 7 9 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 C 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/201006 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 Product Sale_Price 105 85 105 120 95 105 105 100 105 100 100 95 85 85 90 120 85 85 105 105 85 105 90 105 85 2 1 2003 2001 2005 2005 2006 2002 2005 2005 2004 2004 2005 2004 2006 2004 2005 2006 6 2 2 6 6 5 3 11 8 2 10 9 8 8 2001 10 90 95 45 120 100 90 2002 2003 2006 2005 2005 1 12 12 6 9 9 2013 2003 35 20097 2014 2016 2016 85 85 100 100 90 100 90 12 3 2003 2006 2003 2006 2005 2006 2017 1 4 2017 2019 85 90 2019 2001 36 20418 37 20543 38 20277 39 20406 40 20161 41 20229 42 20379 43 20056 44 20080 45 20305 46 20305 47 20305 48 20312 49 20031 50 20223 2020 2021 2021 2021 2021 2021 2001 2003 2001 2005 2001 2004 2003 2002 2001 12 9 12 8 11 3 4 4 95 95 100 95 85 95 105 100 6 4 2021 2022 2022 4 120 1 95 120 6 5 4 1 1 1 3 6 10 4 9 9 2 4 4 11 8 10 200 20202 500g 1 20230 2 20266 3 20452 4 20454 5 20265 6 20404 7 20404 8 20471 9 20513 0 20218 000 1 20307 2 20307 Bono 20227 3 20227 4 20438 5 20375 6 20465 7 20120 CURCU 8 20496 9 20020 0 20024 1 20063 2 20183 3 20183 Board 4 20256 6 20086 8 20179 9 20453 0 20495 i 20040 200g 2 20098 4 20328 5 20384 6 20013 7 20013 8 20089 - 9 20055 9 0 20440 1 20516 2 20280 1/15/20 1006 1/27/20 1006 3/25/20 1007 3/25/20 1006 1/27/20 1006 3/9/20 1006 3/9/20 1006 3/29/20 1007 4/13/20 1007 1/12/20 1007 102 2/11/20 1007 2/11/20 1007 1/14/20 1006 111201006 3/18/20 1007 3/2/20 1007 3/28/20 1006 12/12/19 1006 " We 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 5 6 11 2022 2022 2022 2022 2023 2024 2024 2025 2025 2026 2027 2027 2028 2028 2029 2029 2029 2031 2031 2032 2033 2034 2034 2034 Los 2034 2035 2035 2035 2036 2036 2036 2037 2037 2037 2037 2037 2038 2038 2038 2038 200 2039 2039 2039 2040 2040 Bonn 2040 2040 2040 2041 2042 2042 2043 2002 2004 2004 2003 2004 2003 2006 2003 2001 Koi 2004 2004 200 pos 2005 2001 2006 2006 2004 2005 2005 2004 2003 2004 2003 2002 2001 2004 2004 2005 2005 2004 2006 2005 2003 2003 2006 2006 2006 2003 2003 2003 2002 2004 2005 2004 2001 2002 2006 2001 Koi 2001 500 2004 2003 2006 2004 2004 2001 4 10 7 5 2 11 11 7 7 6 6 2. 3 4 2 4 105 105 100 105 100 90 100 95 105 105 85 OC 95 mo 90 90 90 105 03 85 105 100 105 100 120 95 105 105 85 25 85 105 90 90 85 100 100 90 9 90 100 100 100 120 85 105 95 120 90 95 95 105 100 90 105 4 11 11 10 2 4 4 11/4/19 1007 10 11 3 11 4 7 3 20280 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 4 20378 5 20403 20402 6 20416 7 20138 8 20142 9 20559 00 20017 7 4 2 4 9 11 1 105 95 Sales_Order_Quantity_Sold Product_Sale_Price H K 12 12 10 10 2 1 Customer_ID 2043 2043 2043 2043 2043 2043 2044 2044 2045 2045 2046 2046 2047 2048 Product_Code 2005 2003 2004 2001 2004 2003 2004 2006 2005 2003 2003 2004 2003 2003 2004 2004 2005 2003 2004 Ko 2001 2002 2003 2004 2003 9 12 12 8 6 6 2 9 11 1 2040 2048 2048 6 6 9 85 100 105 95 105 100 105 90 85 85 100 105 100 100 100 105 105 85 100 105 95 120 100 105 105 100 85 85 105 105 105 105 85 95 100 11 8 27137201007 Sales_order_ID Sales_Order_Date M Sales_Employee_ID 20043 11/16/19 1006 20200 1/7/20 1007 20200 1/7/20 1007 20457 3/26/20 1006 20461 3/27/20 1007 ber 20545 4/21/20 1007 ny 20407 3/10/20 1006 20407 3/10/20 1006 20357 2/27/201006 20535 4/19/20 1007 20314 20450 3/24/20 1007 20368 2/29/20 1007 2007 20106 12/7/19 1006 20126 12/16/19 1006 20126 12/16/19 1006 20217 1/12/20 1007 20151 12/22/19 1007 - 20518 4/14/20 1007 20282 1/31/20 1006 2020 20285 2/1/20 1007 2042 20424 3/16/20 1006 20460 3/27/20 1006 20262 1/26/20 1007 20023 11/8/19 1007 20068 LUDO 11/24/19 1007 20249 1/22/20 1006 20539 4/19/20 1006 20037 11/13/19 1007 20037 11/13/19 1007 20001 11/1/19 1006 20137 12/18/19 1006 20155 12/23/19 1006 19 1000 20439 109 w1214 3/19/20 1007 2001 20261 1/25/20 1006 20492 4/8/20 1006 20093 12/2/19 1007 20325 2/15/20 1006 20124 12/15/19 1007 1000 20059 11/22/19 1006 20103 12/5/19 1006 20271 1/28/20 1007 20491 4/7/20 1006 20532 4/17/20 1007 1/6/20 1007 20242 1/19/20 1006 20458 3/26/20 1007 20441 3/19/20 1007 20503 4/10/20 1006 20503 4/10/20 1006 8 7 2 2 10 10 6 12 2 9 8 2005 2048 2049 2049 2050 2050 2050 2050 2051 2052 2052 2053 2053 2054 2054 2056 2056 2056 2056 200 2057 2057 2058 2058 10 3 3 3 10 10 8 1 100 100 100 6 10 100 120 2005 2004 2004 2004 2004 2004 2005 2001 2003 2009 2003 2003 2003 2003 2002 2003 2004 2005 2003 2005 2003 2003 2004 2004 2003 2003 2005 2004 1 7 2059 11 6 2060 2060 2060 2060 2060 2061 2061 2061 2062 2062 2062 7 14 11 3 11 11 12 3 100 105 85 100 85 100 100 105 105 20192 100 100 11 10 6 2 85 105 Required: 1. Which state was the customer from on Sales Order 20001? O Minnesota (MN) Arkansas (AR) O Kansas (KS) O Missouri (MO) 2. How many Sales Orders have customers from Missouri on them? O24 O 16 O 135 O 20 3. What is the total quantity of products sold to customers from Missouri? O 24 O 20 O 16 O 135 4. When creating the VLOOKUP for this alt-lab, which variable has the matching key between the Sales_Transactions and Customers tables? O Product_ID O Product_Description O Customer_ID O Sales Order ID 5. If you replaced the number 5 for the column_num with a 6 (the third argument in the VLOOKUP function), what descriptive information would return instead of Customer_St? Customer_ID Customer_Zip Sales_Order_ID O Sales_Employee_ID Required information (The following information applies to the questions displayed below.) NOTE: Throughout this lab, every time a screenshot is requested, use your computer's screenshot tool, and paste each screenshot to the same Word document. Label each screenshot in accordance to what is noted in the lab. This document with all of the screenshots included should be uploaded through Connect as a Word or PDF document when you have reached the final step of the lab. 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. D E F H J K Product_Code 2004 Sales_Order_Quantity_Sold 12 2005 8 3 5 6 2004 2002 2001 2004 2004 2003 2004 5 2 8 11 3 3 7 3 3 1 8 2003 1 Sales Order_ID 2 20062 3 20168 4 5 20564 6 20140 6 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 34 20393 7 9 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 C 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/201006 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 Product Sale_Price 105 85 105 120 95 105 105 100 105 100 100 95 85 85 90 120 85 85 105 105 85 105 90 105 85 2 1 2003 2001 2005 2005 2006 2002 2005 2005 2004 2004 2005 2004 2006 2004 2005 2006 6 2 2 6 6 5 3 11 8 2 10 9 8 8 2001 10 90 95 45 120 100 90 2002 2003 2006 2005 2005 1 12 12 6 9 9 2013 2003 35 20097 2014 2016 2016 85 85 100 100 90 100 90 12 3 2003 2006 2003 2006 2005 2006 2017 1 4 2017 2019 85 90 2019 2001 36 20418 37 20543 38 20277 39 20406 40 20161 41 20229 42 20379 43 20056 44 20080 45 20305 46 20305 47 20305 48 20312 49 20031 50 20223 2020 2021 2021 2021 2021 2021 2001 2003 2001 2005 2001 2004 2003 2002 2001 12 9 12 8 11 3 4 4 95 95 100 95 85 95 105 100 6 4 2021 2022 2022 4 120 1 95 120 6 5 4 1 1 1 3 6 10 4 9 9 2 4 4 11 8 10 200 20202 500g 1 20230 2 20266 3 20452 4 20454 5 20265 6 20404 7 20404 8 20471 9 20513 0 20218 000 1 20307 2 20307 Bono 20227 3 20227 4 20438 5 20375 6 20465 7 20120 CURCU 8 20496 9 20020 0 20024 1 20063 2 20183 3 20183 Board 4 20256 6 20086 8 20179 9 20453 0 20495 i 20040 200g 2 20098 4 20328 5 20384 6 20013 7 20013 8 20089 - 9 20055 9 0 20440 1 20516 2 20280 1/15/20 1006 1/27/20 1006 3/25/20 1007 3/25/20 1006 1/27/20 1006 3/9/20 1006 3/9/20 1006 3/29/20 1007 4/13/20 1007 1/12/20 1007 102 2/11/20 1007 2/11/20 1007 1/14/20 1006 111201006 3/18/20 1007 3/2/20 1007 3/28/20 1006 12/12/19 1006 " We 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 5 6 11 2022 2022 2022 2022 2023 2024 2024 2025 2025 2026 2027 2027 2028 2028 2029 2029 2029 2031 2031 2032 2033 2034 2034 2034 Los 2034 2035 2035 2035 2036 2036 2036 2037 2037 2037 2037 2037 2038 2038 2038 2038 200 2039 2039 2039 2040 2040 Bonn 2040 2040 2040 2041 2042 2042 2043 2002 2004 2004 2003 2004 2003 2006 2003 2001 Koi 2004 2004 200 pos 2005 2001 2006 2006 2004 2005 2005 2004 2003 2004 2003 2002 2001 2004 2004 2005 2005 2004 2006 2005 2003 2003 2006 2006 2006 2003 2003 2003 2002 2004 2005 2004 2001 2002 2006 2001 Koi 2001 500 2004 2003 2006 2004 2004 2001 4 10 7 5 2 11 11 7 7 6 6 2. 3 4 2 4 105 105 100 105 100 90 100 95 105 105 85 OC 95 mo 90 90 90 105 03 85 105 100 105 100 120 95 105 105 85 25 85 105 90 90 85 100 100 90 9 90 100 100 100 120 85 105 95 120 90 95 95 105 100 90 105 4 11 11 10 2 4 4 11/4/19 1007 10 11 3 11 4 7 3 20280 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 4 20378 5 20403 20402 6 20416 7 20138 8 20142 9 20559 00 20017 7 4 2 4 9 11 1 105 95 Sales_Order_Quantity_Sold Product_Sale_Price H K 12 12 10 10 2 1 Customer_ID 2043 2043 2043 2043 2043 2043 2044 2044 2045 2045 2046 2046 2047 2048 Product_Code 2005 2003 2004 2001 2004 2003 2004 2006 2005 2003 2003 2004 2003 2003 2004 2004 2005 2003 2004 Ko 2001 2002 2003 2004 2003 9 12 12 8 6 6 2 9 11 1 2040 2048 2048 6 6 9 85 100 105 95 105 100 105 90 85 85 100 105 100 100 100 105 105 85 100 105 95 120 100 105 105 100 85 85 105 105 105 105 85 95 100 11 8 27137201007 Sales_order_ID Sales_Order_Date M Sales_Employee_ID 20043 11/16/19 1006 20200 1/7/20 1007 20200 1/7/20 1007 20457 3/26/20 1006 20461 3/27/20 1007 ber 20545 4/21/20 1007 ny 20407 3/10/20 1006 20407 3/10/20 1006 20357 2/27/201006 20535 4/19/20 1007 20314 20450 3/24/20 1007 20368 2/29/20 1007 2007 20106 12/7/19 1006 20126 12/16/19 1006 20126 12/16/19 1006 20217 1/12/20 1007 20151 12/22/19 1007 - 20518 4/14/20 1007 20282 1/31/20 1006 2020 20285 2/1/20 1007 2042 20424 3/16/20 1006 20460 3/27/20 1006 20262 1/26/20 1007 20023 11/8/19 1007 20068 LUDO 11/24/19 1007 20249 1/22/20 1006 20539 4/19/20 1006 20037 11/13/19 1007 20037 11/13/19 1007 20001 11/1/19 1006 20137 12/18/19 1006 20155 12/23/19 1006 19 1000 20439 109 w1214 3/19/20 1007 2001 20261 1/25/20 1006 20492 4/8/20 1006 20093 12/2/19 1007 20325 2/15/20 1006 20124 12/15/19 1007 1000 20059 11/22/19 1006 20103 12/5/19 1006 20271 1/28/20 1007 20491 4/7/20 1006 20532 4/17/20 1007 1/6/20 1007 20242 1/19/20 1006 20458 3/26/20 1007 20441 3/19/20 1007 20503 4/10/20 1006 20503 4/10/20 1006 8 7 2 2 10 10 6 12 2 9 8 2005 2048 2049 2049 2050 2050 2050 2050 2051 2052 2052 2053 2053 2054 2054 2056 2056 2056 2056 200 2057 2057 2058 2058 10 3 3 3 10 10 8 1 100 100 100 6 10 100 120 2005 2004 2004 2004 2004 2004 2005 2001 2003 2009 2003 2003 2003 2003 2002 2003 2004 2005 2003 2005 2003 2003 2004 2004 2003 2003 2005 2004 1 7 2059 11 6 2060 2060 2060 2060 2060 2061 2061 2061 2062 2062 2062 7 14 11 3 11 11 12 3 100 105 85 100 85 100 100 105 105 20192 100 100 11 10 6 2 85 105 Required: 1. Which state was the customer from on Sales Order 20001? O Minnesota (MN) Arkansas (AR) O Kansas (KS) O Missouri (MO) 2. How many Sales Orders have customers from Missouri on them? O24 O 16 O 135 O 20 3. What is the total quantity of products sold to customers from Missouri? O 24 O 20 O 16 O 135 4. When creating the VLOOKUP for this alt-lab, which variable has the matching key between the Sales_Transactions and Customers tables? O Product_ID O Product_Description O Customer_ID O Sales Order ID 5. If you replaced the number 5 for the column_num with a 6 (the third argument in the VLOOKUP function), what descriptive information would return instead of Customer_St? Customer_ID Customer_Zip Sales_Order_ID O Sales_Employee_ID
