Preparing trainee data for data check

Purpose: Ensure that the initial dataset from the client is ready for processing by identifying and eliminating duplicates. This prevents data redundancy, ensures each trainee is uniquely represented, and avoids confusion or errors during import.

1. Prepare the Client Spreadsheet

• Open the nomination spreadsheet from the client.
• Check for duplicates:
  • Use Conditional Formatting > Highlight Cells Rules > Duplicate Values on:
    • ID Number
    • Mobile Number
    • Email Address
  • Remove exact duplicates. If the same info belongs to different people, confirm with the client.

2. Clean the Data

• Names: Use proper case for first and last names.
• Emails: Convert all to lowercase.
• Titles: Remove full stops (e.g., "Mr." → "Mr").
• Miss: Replace with "Ms".
• Format Columns:
  • Mobile: Format as 0000000000 (10 zeros).
  • ID Number: Format as 0000000000000 (13 zeros).
• Standardize Values:
  • Race: Use single-letter codes (A, W, I, C).
  • Gender: Use "F","M" or "U".
  • Remove all special characters from names and emails.

4. Check Setup checklist saved on ticket summary to determine the login method (email vs token)

• If the client has a combination of token and email users, proceed with separation. 

5. Transfer Data to Import Template

• Go to Datacheck Template
  
• Copy the header rows into a new Excel workbook.
• Name the sheet:  ClientName_Programme/ComplianceTraining_Date
• Copy cleaned data from the client's file into this workbook.

6. Final Touches

• For email users, follow the Final touches: Data preparation email users process.
• For token users, follow the Final touches: Data preparation token users process.