Cleaning Data File

The UDF File must be formatted according to the points below to ensure there are no errors when uploading to the client’s instance.

1. Verify all required fields are complete.
2. If the USERID Column is blank, recommend to the client to use the same values found under USERNAME.
3. STATUS Column: Make sure each of the cells contains a value that reads Active or Inactive only.
4. All Columns: Clean up any unnecessary spaces. Each value must have no spaces before, or after. To ensure that there are no spaces:
- a. Add a column to the right and insert the formula =TRIM(Cell)
- b. Drag down to fill the rest of the column, or double click the black square in the bottom right to fill down to the last row
- c. Copy and paste values only into the original column
- d. Delete the column with the TRIM function
5. GENDER Column: Make sure each of the cells contains a value that reads F or M, F for Female and M for Male.
6. EMAIL Column: Each of the values should follow the format <Employee’s Email>@<Company Domain>.
7. MANAGER Column: The USERID of the manager should be entered in this column. Verify that the entries in this column match the manager’s USERID; it should include both the same characters and the same capitalization. (i.e. If the manager’s USERID uses mixed cases, all uppercase, or all lowercase, the Manager’s column should follow the exact same formatting).
- a. To verify the client completed this correctly:
  1. Add a column to the right of the manager column.
  2. Add a second sheet to the workbook, titled Manager USERID.
  3. In the Manager USERID sheet, copy and paste the USERID Column, the First Name, and Last Name Column.
  4. On the first sheet, in the column after manager, enter the formula, =VLOOKUP(I2,'Manager USERID'!$A$2:$C$63,1,FALSE)).
    - 1. This formula includes the range (bolded) located on the second sheet. It may vary on size depending on the number of users.
  5. Verify that a value was returned.
  6. If values appear #N/A, look for errors in the document and fill out a report describing the error using the template below.
    - 1. If “No_Manager” is entered in the column, the formula will return an error. In this case, there is no need to document an error.

Error Template:

Issue #:
Issue Description:
Potential Solution:
Resolution:
Follow up Required (If Yes, describe follow up):

Examples:

Issue #: 1
Issue Description: Sara Smith’s first name is spelled two different ways in the manager column Sarah and Sara.
Potential Solution: I think that ADRAINA is correct because that is what's in the first name column.
Resolution:
Follow up Required (If Yes, describe follow up): Yes, please confirm with client.

Issue #: 2
Issue Description: Clare Parks is listed as John Walker’s Manager, however, Clare is not listed as an active user.
Potential Solution: Upon further inspection, it looks like Johns is the CEO, Maybe John doesn't have a manager at this office. If that is the case complete list NO_MANAGER as the manager.
Resolution:
Follow up Required (If Yes, describe follow up): Yes, is Clare Parks a user, if so, complete a row for her. If not, who is John Walker’s Manager or does she not have one because she is possibly the CEO.

Issue #: 3
Issue Description: Jane Thomas doesn't have a manager, gender or email listed. Upon further inspection, it appears she has the following title, ON CALL RECEPTIONIST.
Potential Solution: Identify her manager, gender and email and complete the row.
Resolution:
Follow up Required (If Yes, describe follow up): Yes, identify Heather's manager gender and email.

b. Note: Sometimes the clients complete this row with the name of the manager instead of the USERID. In that case, we need to use a formula to replace the name with the USERID.
1. Add column to the right of the MANAGER column.
2. Add a second sheet to the workbook, labeled USERID.
3. In the USERID sheet, copy and paste the USERID Column, the First Name, and Last Name Column.
4. Use the formula =CONCATENATE(FirstName, “ “, Last Name)
  - 1. Drag down to fill the rest of the column, or double click the black square in the bottom right to fill down to the last row.
5. Copy and paste the new values into the column so it no longer contains the formula.
6. Delete everything besides USERID and the new column.
  - 1. Column A should contain the original values from the client, and column B should contain the newly formatted version.
7. Use the formula =VLOOKUP(I2,'Manager USERID'!$A$2:$C$63,2,FALSE) to obtain the corrected values. Copy and paste the new values in to appropriate MANAGER Column, then delete the extra row and sheet.

8. HR Column: The USERID of the human resources (HR) representative assigned to the employee should be entered in the column. To verify the client completed this correctly:
- a. Add a column to the right of the HR Rep column.
- b. Add a second sheet to the workbook, titled HR Rep USERID.
- c. In the HR Rep USERID sheet, copy and paste the USERID Column, the First Name, and Last Name Column.
- d. On the first sheet, in the column after HR Rep, enter the formula, =VLOOKUP(I2,'HR Rep USERID'!$A$2:$C$63,1,FALSE)).
  1. This formula includes the range located on the second sheet. It may vary on size depending on the number of users.
- e. Verify that a value was returned.
- f. If values appear #N/A, look for errors in the document and fill out a report describing the error using the template below.
  1. If “No_HR” is entered in the column, the formula will return an error. In this case, there is no need to document an error.
9. HIREDATE Column: Make sure each cells follows the format DD\MM\YYYY.
10. STATE Column: Make sure to use the correct abbreviation for each state.
11. Make sure the file is saved in the format .CSV, not .XLS.