The Compare 2 Sheets feature allows you to perform a detailed comparison between two selected sheets within the same workbook. It highlights differences and provides a configurable way to compare data based on various settings and formatting options. This feature is useful for identifying discrepancies in large datasets, auditing changes, or tracking variations in information.
How to Use
- Open the Compare 2 Sheets tool:
Click the Compare 2 Sheets button under the Workbook Operations category.
- Select Main Sheet:
Choose the Main Sheet from a dropdown list of available sheets in your workbook.
- Select Sub Sheet:
Similarly, select the Sub Sheet from the dropdown list.
- Configure Settings:
- Based on Headers: If checked, the comparison will be made based on the header names of both sheets. If unchecked, the comparison will be based on the cell values directly.
- Comment Mismatch: If checked, any mismatch between the sheets will have a comment added to the cell in the Sub Sheet, indicating the actual value from the Main Sheet.
- Case Sensitive: If checked, the comparison will be case-sensitive, meaning “ABC” and “abc” will be treated as different values.
- Trim: If checked, any leading or trailing spaces in values will be trimmed before comparison.
- Formatting:
- Background Color: Select the background color that will be used to highlight mismatched cells.
- Text Color: Choose the text color for highlighting differences.
- Click Compare:
- After selecting your sheets, settings, and formatting options, click the Compare button.
- If the same sheet names are selected or no sheets are selected, a warning popup will appear prompting the user to select valid sheets.
What Happens Next
- The tool compares data from the Main Sheet and Sub Sheet based on the settings selected:
- Based on Headers: If this option is enabled, comparison is performed by comparing header names across sheets.
- Comment Mismatch: Any cells that have differences will have comments added indicating the value from the Main Sheet.
- Case Sensitive: If this option is enabled, the comparison considers case differences.
- Trim: Leading/trailing spaces in cell values will be ignored during comparison.
- Color Highlights: Mismatched cells are highlighted with the background color and text color you selected. This makes it easy to spot discrepancies in the data.
- Comments: If the “Comment Mismatch” option is checked, a comment will be added in the Sub Sheet for any mismatched values, showing the correct value from the Main Sheet.
How to Interpret the Results
- Highlighting: Cells in the Sub Sheet that differ from the Main Sheet will be visually highlighted with your selected colors.
- Comments: Cells that have discrepancies between the two sheets will show a comment with the correct value from the Main Sheet (if “Comment Mismatch” is selected).
Error Handling and Warnings
- If no sheet names are selected or both sheets are the same, a warning popup will appear reminding you to select different sheets.
- If the “Based on Headers” option is selected, the tool will compare cells by their headers, otherwise, it will compare by cell values.
Use Case Scenarios
- Data Auditing: Compare two versions of a data sheet to spot differences between different report generations.
- Data Quality Checks: Ensure data integrity by comparing datasets after updates, corrections, or data migrations.
- Tracking Changes: Track any changes made to a dataset between two sheets, especially useful for collaborative work.
This feature ensures an easy, configurable comparison process with a focus on data integrity and clear visual feedback.