The Red Bluff Golf Course & Pro Shop manager, Aleeta Herriott, has asked you to create a report that analyzes costs and revenues from tournaments hosted over the past year. In the past, her staff had to reenter data manually from different sources to create this report because no one at the resort knew how to import the data. As a result, they rarely completed the report. Aleeta worries about the accuracy of the reports that were compiled because of the manual data entry. However, she did keep all the original files. Recently, a new Golf database was created to track sales and allow for easy export to Excel for analysis. Aleeta wants you to design a spreadsheet that will help her automate the process of gathering and standardizing the data from the past for analysis.

0.5 The E-mail worksheet contains the names and email addresses of customers. In order to use the e-mail addresses to communicate with customers, they need to be separated from the rest of the associated text and then joined together so that they can be copied and pasted into an e-mail address field. In column C, use the appropriate text function to return the position of the colon character in column B. Resize the column as needed to fit the contents. 0.5 In column D, use the appropriate text function to calculate the total number of characters in each cell of column B. Resize the column as needed to fit the contents. 0.8 In column E use the appropriate text function to extract the e-mail address from column B. Use the number in column C as the relative starting point for the number of characters needed to extract only the e-mail address from the left side of the address data in column B (no colon and no angle brackets included). Resize the column as needed to fit the contents. 0.7 0.7 In cell G2 use the appropriate text function to create an e-mail list that combines the e-mail addresses in column E using a semi-colon and a space (";") for a delimiter. Use the appropriate argument to ignore any blank cells. Additional customer names have been provided on the Members worksheet. They need to be separated into first and last names for communications with those customers. On the Members worksheet, use the Text to Columns Wizard to separate the names in column A so that the first names are in column B and the last names are in column C. A list customers that received golf lessons is contained on the Lessons worksheet. There are duplicate records in the data that need to be removed. On the Lessons worksheet, use Remove Duplicates to remove any records with the same LastName ScheduledDate and Fee. 0.7 0.5 The Invoices worksheet contains a listing of invoice numbers. Any duplicate records need to be identified in the data. On the Invoices worksheet use conditional formatting to highlight the data in column A so that duplicate values appear with a Light Red Fill with Dark Red Text. Turn on filtering for the data and sort by cell color. AutoSave Off Holden_Excel_Ch09_Prepare_Datalntegration20f3.xlsx taylor holden TH T X File Data Review View Help Share Comments Insert v Home Insert Page Layout Formulas X Calibri 11 als BIU AA B- Av General $ %) Conditional Formatting Format as Table Cell Styles 2X Delete Paste 00 60 .00 FO Editing Analyze Sensitivity Data Analysis Sensitivity Format Clipboard Font Alignment Number Styles Cells C2 X B C D E F 1 Email 2 3 4 5 6 7 8 9 10 A Customer ID Full Address Find Colon Length 1 Charles Barker mailto: 2 Missy Malone mailto: 3 Susan Winter mailto: 4 Jose Rodriquez mailto: 5 Nicole Rodriquez mailto: 6 Brenden Erickson mailto: 8 Courtney Miller mailto: 9 Fiona Britt mailto: 10 Robert Allen mailto: 11 John Trujillo mailto: 12 Ian McShane mailto: 13 Marge Fresquez mailto: 14 Marcus Maestas mailto: 15 Janice Marquardt mailto: 16 Keith Marquardt mailto: 17 Kate Neidhart mailto: 18 Thomas Reed mailto: 19 Cynthia Reid mailto: 20 Joseph McMannon mailto: 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 RegistrationData CustNames E-mail Members Less ... + Ready + 100% 0.5 The E-mail worksheet contains the names and email addresses of customers. In order to use the e-mail addresses to communicate with customers, they need to be separated from the rest of the associated text and then joined together so that they can be copied and pasted into an e-mail address field. In column C, use the appropriate text function to return the position of the colon character in column B. Resize the column as needed to fit the contents. 0.5 In column D, use the appropriate text function to calculate the total number of characters in each cell of column B. Resize the column as needed to fit the contents. 0.8 In column E use the appropriate text function to extract the e-mail address from column B. Use the number in column C as the relative starting point for the number of characters needed to extract only the e-mail address from the left side of the address data in column B (no colon and no angle brackets included). Resize the column as needed to fit the contents. 0.7 0.7 In cell G2 use the appropriate text function to create an e-mail list that combines the e-mail addresses in column E using a semi-colon and a space (";") for a delimiter. Use the appropriate argument to ignore any blank cells. Additional customer names have been provided on the Members worksheet. They need to be separated into first and last names for communications with those customers. On the Members worksheet, use the Text to Columns Wizard to separate the names in column A so that the first names are in column B and the last names are in column C. A list customers that received golf lessons is contained on the Lessons worksheet. There are duplicate records in the data that need to be removed. On the Lessons worksheet, use Remove Duplicates to remove any records with the same LastName ScheduledDate and Fee. 0.7 0.5 The Invoices worksheet contains a listing of invoice numbers. Any duplicate records need to be identified in the data. On the Invoices worksheet use conditional formatting to highlight the data in column A so that duplicate values appear with a Light Red Fill with Dark Red Text. Turn on filtering for the data and sort by cell color. AutoSave Off Holden_Excel_Ch09_Prepare_Datalntegration20f3.xlsx taylor holden TH T X File Data Review View Help Share Comments Insert v Home Insert Page Layout Formulas X Calibri 11 als BIU AA B- Av General $ %) Conditional Formatting Format as Table Cell Styles 2X Delete Paste 00 60 .00 FO Editing Analyze Sensitivity Data Analysis Sensitivity Format Clipboard Font Alignment Number Styles Cells C2 X B C D E F 1 Email 2 3 4 5 6 7 8 9 10 A Customer ID Full Address Find Colon Length 1 Charles Barker mailto: 2 Missy Malone mailto: 3 Susan Winter mailto: 4 Jose Rodriquez mailto: 5 Nicole Rodriquez mailto: 6 Brenden Erickson mailto: 8 Courtney Miller mailto: 9 Fiona Britt mailto: 10 Robert Allen mailto: 11 John Trujillo mailto: 12 Ian McShane mailto: 13 Marge Fresquez mailto: 14 Marcus Maestas mailto: 15 Janice Marquardt mailto: 16 Keith Marquardt mailto: 17 Kate Neidhart mailto: 18 Thomas Reed mailto: 19 Cynthia Reid mailto: 20 Joseph McMannon mailto: 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 RegistrationData CustNames E-mail Members Less ... + Ready + 100%