Comparing Data in Excel Files: A Tutorial

This document outlines three real-life methods to compare data in Excel files, highlighting differences and identifying duplicates.

Introduction

• Discussion on the idea of "bumping" files together.
• Tutorial video covers three methods:
  • Using XLOOKUP to find missing data.
  • Using IF formulas and conditional formatting to detect changes.
  • Using Excel's highlight duplicates feature.

Method 1: Finding Missing Data with XLOOKUP

1. Setup:
   • Load work assignment data and count records.
   • Compare with employee list to find discrepancies.
2. Process:
   • Insert column and title it "Missing Work Assignment".
   • Use XLOOKUP to compare Employee IDs between two sheets.
3. Outcome:
   • Identify missing employee work assignments.
   • Use count feature to verify missing entries.

Method 2: Detecting Changes with IF Formulas and Conditional Formatting

1. Setup:
   • Compare two demographic reports with identical structure.
2. Using IF Formula:
   • Insert old salary data next to new report.
   • Use IF to compare salaries, labeling changes as "Changed".
3. Using Conditional Formatting:
   • Highlight changes by comparing data.
   • Use filter options to view only changed entries.

Method 3: Detecting Duplicates

1. Justification:
   • Importance of cleaning job codes by removing letters.
2. Process:
   • Sort job codes and insert a new column without letters.
   • Paste values and use conditional formatting to highlight duplicates.
3. Outcome:
   • Identify duplicate values needing new job codes.

Conclusion

• Recap on various strategies for comparing files in Excel.
• Encouragement for viewers to share their methods.
• Promotion of Sharon Smith's HR tutorials and resources.

For further resources and guidance, visit SharonSmithHR.com.