Problem 1 [15 marks]: Purpose of this problem is to create a macro which will split the details of the people who work i

[answerhappygod]

Problem 1 [15 marks]: Purpose of this problem is to create a macro which will split the details of the people who work in your company into three columns: Name, City, and Year of Birth. Two support columns will also be created to act as intermediary values for the macro. Instructions: To complete this assignment, you must start with the file “Project4-GRP3-Q1- StartFile.xlsx”. Using this file, perform the following tasks:
1. If the Developer tab is not displayed on the Ribbon, then activate it as explained in the course notes and during the lecture.
2. The first step is to figure out how to split the data in column A into the parts needed for columns B, C, D, F, G. You need to be comfortable with the formulas before creating the macro. Solve each part in the following order:
a) Cell F3 needs to contain the character position of the first colon in cell A3. To solve this requirement, use the FIND() function. The correct answer for the first entry (Darius Pennington:Liernu:1988) will be 18.
b) Cell G3 needs to contain the character position of the second colon in cell A3. To solve this, you again need to use the FIND() function. This time, you need to start the search at the first character after the position of the first colon (the position you found in cell F3 plus one). Recall that the FIND() function has three parameters; the first two are required, and the third one is the position to start the search. By starting at the character after the first colon (F3+1), you will find the second colon. For the first entry (Darius Pennington:Liernu:1988), this value will be 25.
c) Cell B3 will contain the name. This will be created using the LEFT() function along with the position of the first colon which you already calculated in cell F3 (make sure you subtract 1 from the value in F2 so you do not also take the colon). The answer is the left 12 characters of cell A2, which for the first entry will be the person’s name Darius Pennington. COSC1702 – Project #4
d) Cell D3 will contain the Year the user was born. There are technically 2 ways to do this, and both are acceptable. Both ways start out the same, and will have you using the RIGHT() function. The first way will have you calculate the difference between the length of the string and the position of the second colon. In our first entry (Darius Pennington:Liernu:1988). Using the LEN() function, you can determine the length to be 23; subtract the position of the second colon (in cell G3) and you will have the number of characters to keep from the right side of the first entry. For this first entry, the value will work out to be 4.
What you will notice is that while all of the strings in this example will be of different lengths, the difference between the length of the string and the position of the second colon should always be 4 because it is always a 4-digit year. Using this fact, the second way to do this rather than doing the calculation is to always use 4 as the second parameter of the RIGHT() function. The risk in this approach is that a stray space at then end of an entry would result in the wrong answer; this makes the first approach better.
e) The value in cell C3 needs to be the name of the City the user was born. This calculation is a bit more complicated than the other ones. To isolate the name, you need to first find the text without the user’s Name and first colon. This is found using the RIGHT() function in a similar manner as was used to find the Year – except the position of the first colon is used instead of the second colon. Once you have figured out this function, you can use this as the ‘text’ parameter of a LEFT() function – in other words, the RIGHT() function will be nested inside a LEFT() function. The number of characters you want for the LEFT() function is equal to the difference between the second colon and the first colon (G3 – F3). The result will be one character too long – so subtract one and you will get the City name.
f) You can test that your functions on the next entry (Deacon Ramos:Paiguano:1992) by copying your functions in row 3 into their corresponding cells in row 3 (copy B2:G2 into cells B3:G3). You should have the following results:
B4 = Deacon Ramos
C4 = Paiguano
D4 = 1992
F4 = 13
G4 = 22
3. Once you comfortable with the formulas from step 2, either print the formulas or copy them down somewhere. You will need these during the creation of the macro. I would also strongly suggest two or three practice runs at entering the formulas again since you will be recording your actions for the macro and you want to get it correct the first time.
4. Create a macro that splits the User information in the active cell by doing the following:
a) Make sure the active cell is the first entry to be split = specifically A3. Also, make sure you are creating a macro which uses relative references.
b) Click on the “Use Relative References” and ensure that it is shaded grey. Then click the Record Macro button (Developer Tab | Code group).
c) When the Record Macro dialog box appears, name the macro ‘SplitEntry’, assign the shortcut key Ctrl+s, store the macro in this workbook, enter your name in the Description box followed by a colon and the text “Splits the User’s information in the active cell into three parts: Name, City and Year. The three parts are placed in the adjoining cells to the left.”.
d) Click the OK button to start the macro recording process.
5. Perform the following actions:
a) Move across the row to column F (you should be in F3) and enter the formula for finding the position of the first colon.
b) Then move one column to the right (now in column G3) and enter the formula for the position of the second colon.
c) Next enter the function to isolate the Name in cell B3 followed by the function to isolate the Year in cell D3. d Lastly, enter the function to isolate the name of the User in cell C3.
f) Next, copy the cells B2:G3 and then paste the values over top of the functions. When done, each of the cells B3, C3, D3, F3 and G3 will contain the results of the functions and not the functions anymore.
g) As the final step, make cell A4 the active cell. Then stop the Macro recording.
HINT: I suggest practicing the process several times before creating the macro. You can delete any macros which does not work from the Macros Dialog box.
5. Click the Macros button (Developer tab | Code group) to display the Macro dialog box. Run the SplitEntry macro to split the next Name entry (make sure the active cell is A3).
6. Test the shortcut for the Macro by typing Ctrl+S while the active cell is over the third entry.
7. Right-click the Quick Access Toolbar and then click Customize Quick Access Toolbar. When the Excel Options dialog box is displayed, click the ‘Choose commands from’ box arrow and choose Macros. Click ‘SplitEntry’, click the Add button. Select ‘SplitEntry’ from the right column and then click the Modify button below the list – from the dialog, pick a new icon for this Quick-Shortcut.
Then click the OK button to close the Excel Options dialog box.
8. Run the macro as follows:
(a) click the ‘SplitEntry’ button on the Quick Access Toolbar to split the next entry; and
(b) press Ctrl+S to split the next entry. Keep executing the macro until all the entries have been split.
10. Save the file again (make sure it saves with the macro) and then submit your file to CMS