How to Find Missing CRM Records Using Excel Right Anti Join
Question details
Identify records found in an old CRM export but missing from the current system by using an Excel Power Query join.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing an old CRM data export against a current system export to isolate unmatched or missing records.
- Observed behavior
- Successfully returning unmatched records from the old system table using a Right Anti Join in Power Query.
Ensure both the old CRM export and the new CRM export contain a shared unique identifier, such as a record number, invoice number, or phone number, and are saved in an accessible location.
Use Power Query Right Anti Join to Find Missing Records
Merge both CRM data lists as queries and apply a Right Anti Join to filter out all matched data, leaving only the records missing from your new system.
By formatting your data as Excel tables, you can load them directly into Power Query. A Right Anti Join is specifically designed to look at a left table (new system) and a right table (old system), returning only the rows from the right table that do not match anything in the left table.
Select the data in your new CRM export and press Ctrl+T to format it as a table. Repeat this process for the old CRM export list. Give both tables identifiable names.
Click anywhere inside the new-system table, go to the Data tab, and select 'From Table/Range'. In the Power Query Editor, click 'Close & Load To...' and choose 'Only Create Connection'. Do the same for the old-system table.
Navigate back to the Data tab, select 'Get Data', then 'Combine Queries', and choose 'Merge'. Select your new-system table as the first (left) table and the old-system table as the second (right) table.
Click the column header of your shared identifier (e.g., phone number or record number) in both table previews to highlight them. Under the 'Join Kind' dropdown menu, select 'Right Anti (rows only in second)'.
Click OK to execute the merge. Power Query will display the unmatched rows from your old system. Click 'Close & Load' on the Home tab to output this list of missing CRM records onto a new worksheet.
Manage and Compare CRM Spreadsheets with WPS Office
While Power Query is a specific component of Microsoft Excel, WPS Office provides a free, lightweight alternative with powerful data handling, VLOOKUP functions, and seamless Microsoft Office format compatibility for everyday data comparison.
- 1. Download and Install: Download WPS Office Free from the official website and follow the simple installation instructions.
- 2. Open your CRM Data: Launch WPS Spreadsheet and seamlessly open your existing Microsoft Excel files containing your old and new CRM exports.
- 3. Compare Data with Formulas: Use powerful built-in functions like VLOOKUP or Conditional Formatting to quickly identify missing records between your datasets.
Frequently Asked Questions
What exactly does a Right Anti Join do in Power Query?
A Right Anti Join compares two tables and extracts only the rows from the second (right) table that have no matching counterpart in the first (left) table. It is highly useful for finding missing data or discrepancies.
Can I use names or phone numbers as the matching field?
Yes. You can use any shared field, such as phone numbers, full names, invoice numbers, or purchase order numbers. However, the data must be formatted identically (e.g., no extra spaces or formatting differences) for the match to work accurately.
Why is my Power Query returning no results or incorrect results?
This usually happens if the data types of your matching columns differ (for instance, one is formatted as Text and the other as Whole Number), or if there are hidden trailing spaces. Use the Trim and Clean tools within Power Query on your identifier columns before performing the merge.
