Match and Combine Data from Two Excel Sheets with Similar Names
Question details
The user needs to merge PC hardware and user information from two different Excel sheets where the primary matching key (PC names) contains duplicates and slight textual variations.
- Product
- Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- Consolidating a large dataset of approximately 1,400 computers from two sheets into one master list, requiring deduplication and standardizing slightly mismatched PC names to correctly append Windows build, location, and device type.
- Observed behavior
- The PC names contain inconsistent text formatting and duplicate records, preventing a direct 1:1 data merge using standard lookup formulas without prior data cleaning or advanced fuzzy matching.
Before modifying or deduplicating your dataset, ensure you create a copy of your original workbook to prevent accidental data loss during the merging process.
Standardize PC Names and Match Data Using XLOOKUP
Use built-in Excel functions to clean up formatting inconsistencies and remove duplicates before merging data with XLOOKUP. This is ideal when PC names can be made perfectly identical.
Before performing any lookup between two sheets, it is crucial to ensure your matching keys (in this case, PC Names) are formatted identically. Even a single hidden space can cause standard formulas to fail.
In a new helper column, use a formula like =UPPER(TRIM(CLEAN(A2))) to remove extra spaces, strip out non-printable characters, and standardize the capitalization of your PC names.
Select your cleaned dataset, navigate to the Data tab, and click "Remove Duplicates". Ensure you first sort the data to retain the most complete or recent record.
In Sheet 2, type the formula =XLOOKUP([@[PC Name]],Sheet1[PC Name],Sheet1[Windows Build],"Not found") to pull the specific data columns you need from Sheet 1.
Merge Sheets Using Power Query Fuzzy Matching
If PC names remain slightly different even after cleaning (e.g., minor typos), Power Query's fuzzy merge feature can match them based on a similarity threshold.
Troubleshoot Power Query Errors
Resolve issues where a query works perfectly in a test workbook but fails or returns errors when applied to the original main file.
Consolidate and Match Sheet Data Easily with WPS Spreadsheet
WPS Spreadsheet offers powerful data handling tools, including seamless XLOOKUP support and one-click duplicate removal. You can accurately standardize and merge large datasets across multiple sheets without dealing with complex query errors.
- 1. Open your datasets: Launch WPS Spreadsheet and open the workbooks containing your PC inventory lists.
- 2. Clean and deduplicate: Use the built-in Text tools and "Remove Duplicates" feature located in the Data tab to standardize your primary columns.
- 3. Merge using formulas: Insert the XLOOKUP function to seamlessly pull and match hardware details across different sheets.
Frequently Asked Questions
Why does my XLOOKUP return 'Not found' when names look the same?
This usually happens due to invisible trailing spaces, non-printable characters, or case mismatches. Wrap your cell references in TRIM and CLEAN functions to standardize the text before matching.
What is a Fuzzy Merge in Power Query?
Fuzzy matching is a feature in Power Query that identifies and merges rows containing text that is similar but not exactly identical, allowing you to bypass minor typos or inconsistent naming conventions without strict 1:1 matches.
How do I handle duplicate PC names when merging?
Before merging, you must define a business rule to deduplicate your data. Decide whether to keep the newest record or the row with the most complete information, sort your data accordingly, and use the Remove Duplicates tool to leave only unique identifiers.
Why does a query work in a test workbook but fail in my original file?
Errors often occur due to mismatched table names, slightly different column headers, or differing data types between the two workbooks. Always check for hidden spaces in headers and ensure data types match perfectly in the Power Query Editor.
