You are to clean up a book list into appropriate format. Titles are to be reformatted by putting any leading "The" at the end of the title and author names are to be reformatted into a consistent format. The input data can have "sloppy" spacing and capitalization and need to be cleaned up.
As for all spreadsheet assignments, the input will be on worksheets that are not to have any computational work done on them and the final results will be on a worksheet that will be given a specific name. You will be given two sets of sample data to work with, but your work will be graded with different data. It is therefore critical that you NOT do any work on the input sheet and that you not assume any of the data will stay the same. What WILL remain the same is the number of data entries and the location of all information.
You will be given a worksheet titled books. This worksheet will have two columns: the first is the author and the second is the book name.
You are to create a new worksheet titled booklist. This worksheet will have two columns: Author and Title. This is where the corrected book and author information will go.
The authors may have leading and extraneous blanks and may have any (or no) capitalization. There will only be a single author for each book. The author's name can be in any of several formats:
There will never be more than one middle name.
You are to extract only the last name of the author. They are to be properly capitalized as defined by the PROPER function regardless of form on the book sheet. If there are capital letters in the middle of a name, they are to be turned into lower case.
The title may or may not have "the" placed at the end and its spacing and capitalization have not been cleaned up. You are to make sure that any leading "The" is moved to the end of the title, preceded by a comma (,). Any leading and extraneous blanks are to be removed and all of the capitalization is to be correct as defined by the PROPER function with one exception. Because the PROPER function incorrectly capitalizes the letter after ', you are to return it to lower case. There will be no more than one ' in any title.
The first set of sample input and the expected output can be downloaded from sample.xlsx and sample.pdf. The second set of sample input and the expected output can be downloaded from sample2.xlsx and sample2.pdf.
To help both you and me, I strongly encourage you to label all columns on your intermediate sheets and I encourage you to process the fields in small steps. Specifically, use the technique that we talked about in class of doing the computation for each possible alternative and then simply choose the correct one. Note also that we have worked on each of the pieces of this assignment in class. You simply need to put them together. It may be helpful to do different functions on different worksheets. For example, your intermediate worksheets might do the following:
The web page that you are to build is to include the properly formatted booklist table. The table is to have no visible borders and is to have the headers in a different style. You are to have an appropriate header on the web page. The data that you are to use for this page can be found at assign9_web.xlsx.
Grading Criteria: Grading for this assignment is based primarily on the correctness of the results. Readability of the results will also be considered.