Grader – Instructions Excel 2019 Project
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.
| Step | Instructions | Points Possible |
| 1 | This exercise begins on page 519 of your text. Start Excel. Download and open the file named Excel_Ch09_Prepare_DataIntegration2of3.xlsx. Grader has automatically added your last name to the beginning of the filename. | 0 |
| 2 | A list of customer information on a tournament golf club giveaway has been provided in the Excel_Ch09_Prepare_DataIntegration2of3.xlsx workbook. The data needs to be cleansed before it can be used for purposes such as analysis or merging to a Word document. On the RegistrationData worksheet in column B, use Flash Fill to separate the first name from the name in column A. | 0.4 |
| 3 | The dates in column C are not stored in date formats that Excel can use. In column D, use Flash Fill to convert the data in column C into a month/day/year date format e.g., 4/13/2022, 3/18/2022, etc. | 0.6 |
| 4 | The phone numbers provided in column E are not in a standard phone number format. In to include these phone numbers in mail merges or other professional formats they need to be formatted using a hyphen. In column F, use Flash Fill to convert the string of numbers in column E to include a hyphen in between the first 3 digits and the last 4 digits e.g., 555-6199, 555-1953, etc. | 0.6 |
| 5 | The customer names on the CustNames worksheet were imported with non-printable characters, extra spaces, and in inconsistent formats. The data needs to be cleansed to appear as first name, middle initial (if present), and last name. On the CustNames worksheet, in column B, use the appropriate text function to clean all the nonprinting characters from the data in column A. Resize the column as needed to fit the contents. | 0.5 |
| 6 | The names should also be proper cased. In column C, use the appropriate text function to convert the data in column B into proper case. Resize the column as needed to fit the contents. | 0.5 |
| 7 | In cell D2, use the appropriate text function to remove any extra spaces before or after as well as between the names in column C. Resize the column as needed to fit the contents. | 0.5 |
| 8 | In column E, use the appropriate text function to return the position of the space character located in the data in column D. Resize the column as needed to fit the contents. | 0.6 |
| 9 | In column F, use the appropriate text function to extract only the last name from column D. Use the number in column E as the relative starting point for the number of characters needed to extract only the last name from the left side of the data in column D (no extra spaces). Resize the column as needed to fit the contents. | 0.6 |
| 10 | In column G, use the appropriate text functions to extract only the first name and middle initial from column D. Use the value in column E as part of the calculation to determine the number of characters needed to extract only the first name and middle initial from the right side of the data in column D. Resize the column as needed to fit the contents. | 0.8 |
| 11 | In column H, use the ampersand symbol (&) to concatenate the first name and middle initial in column G and the last name in column F with a space in between. Resize the column as needed to fit the contents. | 0.5 |
| 12 | The E-mail worksheet contains the names and email addresses of customers. In 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 |
| 13 | 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.5 |
| 14 | 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.8 |
| 15 | 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. | 0.7 |
| 16 | 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. | 0.7 |
| 17 | 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 |
| 18 | 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. | 0.5 |
| 19 | Save and close Excel_Ch09_Prepare_DataIntegration2of3. Exit Excel. Submit your files as directed. Please note: This project continues in part C which is a separate Grader project. | 0 |
| Total Points | 10 |
Created On: 12/18/2019 1 YO19_Excel_Ch09_Prepare – Data Integration Part B 1.0
| First Name & Middle Initial | First Name | Registration Date | Cleansed Registration Date | Phone Number | Cleansed Phone Number |
| Carter X | 20220413 | 5556199 | |||
| Jane | 20220318 | 5551953 | |||
| Fitzgerald K | 20220410 | 5557584 | |||
| Arden W | 20220312 | 5558974 | |||
| Zeph | 20220322 | 5551491 | |||
| Lilah | 20220302 | 5557887 | |||
| Bruno L | 20220309 | 5553920 | |||
| Kiara | 20220407 | 5556312 | |||
| Yardley Y | 20220423 | 5555604 | |||
| Maxine | 20220419 | 5556216 | |||
| Walter M | 20220309 | 5551513 | |||
| Dexter | 20220325 | 5558772 | |||
| Asher | 20220402 | 5559808 | |||
| Ruby I | 20220316 | 5551784 | |||
| Keaton T | 20220321 | 5551795 | |||
| Kiona | 20220315 | 5556175 | |||
| Kermit V | 20220325 | 5556964 | |||
| Christen | 20220301 | 5557674 | |||
| Coby | 20220305 | 5553561 | |||
| Cullen F | 20220319 | 5556467 | |||
| Desiree J | 20220325 | 5558565 | |||
| Seth | 20220308 | 5554393 | |||
| Alden | 20220401 | 5556732 | |||
| Germaine | 20220325 | 5551342 | |||
| Maxwell E | 20220308 | 5558603 |
| Name | Clean | Proper | Trim | Find | Last Name | First Name & Middle Initial | Full Name |
| ��GAINES THOMAS M. | |||||||
| �Stone Jerome� | |||||||
| �� Cannon Simone Z� | |||||||
| ��Campbell Reece H.� | |||||||
| ��Chase Stafford� | |||||||
| �Mcclain Jack� | |||||||
| �JONES BOBBY J.� | |||||||
| � Davis Tanner� | |||||||
| �Murray Colt Q .� | |||||||
| � GIFFORD MARJORIE� | |||||||
| � HANKINS ELIZA S.� | |||||||
| �Branch Anthony | |||||||
| � Bray Brian� | |||||||
| � JOYCE DAVID J.� | |||||||
| ��KRAUS CAROL T. | |||||||
| ��LAMB SAMANTHA | |||||||
| � Kirk Jocelyn B. | |||||||
| ��LEWIS PATRICK� | |||||||
| �� Gentry David | |||||||
| �� PREWITT HANNAH E.� | |||||||
| ��RANGELL JOHN W. | |||||||
| � RUSSO ROBERT | |||||||
| ��TATE ANDREA M.� | |||||||
| � THOMPSON SHARON� | |||||||
| �� WOODFORD VERA T. |
| Customer ID | Full Address | Find Colon | Length | Create an email list separated by semi-colons | |
| 1 | Charles Barker mailto: <[email protected]> | ||||
| 2 | Missy Malone mailto: <[email protected]> | ||||
| 3 | Susan Winter mailto: <[email protected]> | ||||
| 4 | Jose Rodriquez mailto: <[email protected]> | ||||
| 5 | Nicole Rodriquez mailto: <[email protected]> | ||||
| 6 | Brenden Erickson mailto: <[email protected]> | ||||
| 7 | Kaseem Castillo mailto: <[email protected]> | ||||
| 8 | Courtney Miller mailto: <[email protected]> | ||||
| 9 | Fiona Britt mailto: <[email protected]> | ||||
| 10 | Robert Allen mailto: <[email protected]> | ||||
| 11 | John Trujillo mailto: <[email protected]> | ||||
| 12 | Ian McShane mailto: <[email protected]> | ||||
| 13 | Marge Fresquez mailto: <[email protected]> | ||||
| 14 | Marcus Maestas mailto: <[email protected]> | ||||
| 15 | Janice Marquardt mailto: <[email protected]> | ||||
| 16 | Keith Marquardt mailto: <[email protected]> | ||||
| 17 | Kate Neidhart mailto: <[email protected]> | ||||
| 18 | Thomas Reed mailto: <[email protected]> | ||||
| 19 | Cynthia Reid mailto: <[email protected]> | ||||
| 20 | Joseph McMannon mailto: <[email protected]> |
| Names | First Name | Last Name |
| Thomas Gaines | ||
| Jerome Stone | ||
| Simone Cannon | ||
| Reece Campbell | ||
| Stafford Chase | ||
| Jack Mcclain | ||
| Bobby Jones | ||
| Tanner Davis | ||
| Colt Murray | ||
| Marjorie Gifford | ||
| Eliza Hankins | ||
| Anthony Branch | ||
| Brian Bray | ||
| David Joyce |
| LastName | ScheduledDate | Fee |
| Winter | 7/22/22 | $175.00 |
| Barker | 6/8/22 | $175.00 |
| Miller | 5/27/22 | $145.00 |
| Malone | 6/8/22 | $200.00 |
| Barker | 6/17/22 | $325.00 |
| Castillo | 5/30/22 | $75.00 |
| Rodriquez | 5/11/22 | $200.00 |
| Miller | 5/1/22 | $175.00 |
| Miller | 5/27/22 | $145.00 |
| Britt | 5/17/22 | $125.00 |
| Rodriquez | 5/12/22 | $200.00 |
| Barker | 6/2/22 | $100.00 |
| Castillo | 5/31/22 | $300.00 |
| Winter | 7/23/22 | $300.00 |
| Barker | 6/8/22 | $175.00 |
| Allen | 6/10/22 | $300.00 |
| Rodriquez | 5/11/22 | $200.00 |
| Invoice Numbers |
| 13742 |
| 14618 |
| 12761 |
| 15056 |
| 12072 |
| 13389 |
| 15436 |
| 12905 |
| 14371 |
| 14668 |
| 12627 |
| 12948 |
| 13360 |
| 13844 |
| 12042 |
| 12381 |
| 15076 |
| 14532 |
| 14038 |
| 13292 |
| 12001 |
| 12275 |
| 14449 |
| 11902 |
| 14162 |
| 12558 |
| 13254 |
| 12424 |
| 14974 |
| 13912 |
| 15148 |
| 14304 |
| 13674 |
| 13141 |
| 14666 |
| 13184 |
| 13329 |
| 13907 |
| 12255 |
| 14261 |
| 13033 |
| 12701 |
| 14223 |
| 12715 |
| 14539 |
| 12042 |
| 14161 |
| 13942 |
| 12924 |
| 12445 |
| 14248 |
| 13123 |
| 14090 |
| 15355 |
| 14412 |
| 12676 |
| 13838 |
| 14449 |
| 14849 |
| 12662 |
| 11864 |
| 13935 |
| 12470 |
| 15059 |
| 15387 |
| 15506 |
| 12888 |
| 12559 |
| 13969 |
| 13215 |
| 12772 |
| 12827 |
| 12949 |
| 15138 |
| 12359 |
| 12614 |
| 15524 |
| 13430 |
| 12439 |
| 14876 |
| 12979 |
| 14803 |
| 14575 |
| 12045 |
| 13176 |
| 12767 |
| 15054 |
| 13080 |
| 13044 |
| 11972 |
| 13109 |
| 13861 |
| 14769 |
| 13277 |
| 15199 |
| 14431 |
| 12831 |
| 15478 |
| 13270 |
| 14112 |
| 12872 |
| 13073 |
| 13188 |
| 15563 |
| 12918 |
| 12215 |
| 14007 |
| 15053 |
| 12426 |
| 14719 |
| 13164 |
| 12530 |
| 15587 |
| 12706 |
| 15320 |
| 12583 |
| 14002 |
| 14162 |
| 12596 |
| 13316 |
| 14351 |
| 12296 |
| 13554 |
| 15232 |
| 14837 |
| 14320 |
| 13291 |
| 14847 |
| 12272 |
| 13262 |
| 14979 |
| 12675 |
| 12730 |
| 13998 |
| 14218 |
| 12967 |
| 12772 |
| 13673 |
| 14216 |
| 14318 |
| 15337 |
| 14577 |
| 12677 |
| 13566 |
| 14676 |
| 14593 |
| 14562 |
| 14554 |
| 12009 |
| 11928 |
| 13909 |
| 15351 |
| 13400 |
| 14211 |
| 15213 |
| 11921 |
| 13509 |
| 13636 |
| 14255 |
| 14392 |
| 13159 |
| 11968 |
| 12446 |
| 12822 |
| 15587 |
| 12791 |
| 12426 |
| 11963 |
| 13813 |
| 12679 |
| 12279 |
| 14996 |
| 13016 |
| 14839 |
| 15192 |
| 13747 |
| 13385 |
| 14810 |
| 12053 |
| 15517 |
| 14247 |
| 14307 |
| 15263 |
| 15591 |
| 15313 |
| 12959 |
| 11953 |
| 13360 |
| 12841 |
| 11974 |
| 12695 |
| 14794 |
| 13186 |
| 12338 |
| 14239 |
| 14099 |
| 12501 |
| 13941 |
| 15575 |
| 15567 |
| 12554 |
| 15078 |
| 13296 |
| 12730 |
| 13369 |
| 12592 |
| 12881 |
| 13829 |
| 12777 |
| 14007 |
| 12517 |
| 13388 |
| 15231 |
| 12179 |
| 12146 |
| 15246 |
| 13630 |
| 13741 |
| 15538 |
| 14295 |
| 14563 |
| 13322 |
| 13891 |
| 13016 |
| 13847 |
| 15236 |
| 13954 |
| 14090 |
| 12485 |
| 12190 |
| 13103 |
| 14920 |
| 13829 |
| 12475 |
| 14176 |
| 14120 |
| 13802 |
| 12085 |
| 12860 |
| 13189 |
| 12099 |
| 14423 |
| 12794 |
| 14466 |
| 15036 |
| 12870 |
| 13412 |
| 13853 |
| 15258 |
| 14381 |
| 13944 |
| 14163 |
| 13033 |
| 14164 |
| 15518 |
| 15523 |
| 13906 |
| 15501 |
| 12544 |
| 14148 |
| 14515 |
| 14359 |
| 12889 |
| 12722 |
| 14807 |
| 14994 |
| 11821 |
| 12944 |
| 13935 |
| 13608 |
| 12668 |
| 14531 |
| 11820 |
| 12193 |
| 15148 |
| 13330 |
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.
Read moreEach paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.
Read moreThanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.
Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.
Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.
Read more