How to Group Similar Customer Account Names in Excel
Question details
The user needs a method to match and group similar customer account names that contain minor variations, abbreviations, or LLC suffixes.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Cleaning up and standardizing customer data by grouping variations of the same account name into a single group identifier.
- Observed behavior
- Customer names are fragmented due to inconsistent data entry and minor text variations, making it difficult to analyze accounts comprehensively.
Ensure your customer data is formatted as an Excel Table. It is highly recommended to create a backup of your original dataset before applying any fuzzy matching transformations, as approximate matches require manual review.
Use Power Query Fuzzy Merge to Group Names
Power Query includes a built-in fuzzy matching feature that is perfect for grouping similar text entries based on an adjustable similarity threshold.
Fuzzy matching in Power Query allows you to compare two lists of text and merge them based on how similar they are, rather than requiring an exact match. This is ideal for handling variations like 'Inc.', 'LLC', or typos.
Select your customer data and press Ctrl+T to format it as a Table. Then, navigate to the 'Data' tab and click 'From Table/Range' to open the Power Query Editor.
In the Queries pane on the left, right-click your query and select 'Duplicate'. This creates a reference list that you will merge against your original list.
Select your original query. On the Home tab, click 'Merge Queries'. In the dialog box, select your duplicated query in the second drop-down. Highlight the customer name column in both tables, and check the box for 'Use fuzzy matching to perform the merge'.
Expand the 'Fuzzy matching options' section. Enter a similarity threshold between 0.00 and 1.00 (e.g., 0.80 is a good starting point). You can also choose to ignore case or ignore text parts.
Click 'OK' to apply the merge. Expand the new column to show the matched group identifiers, then click 'Close & Load' on the Home tab to export the results back to Excel.
Group Names Using the Fuzzy Lookup Add-In
If you are using an older version of Excel without Power Query's fuzzy merge, you can use Microsoft's free Fuzzy Lookup Add-In.
Try WPS Office for Your Data Management Needs
While advanced fuzzy matching relies on specific Microsoft Excel add-ins or Power Query features, WPS Office offers a free, lightweight, and highly compatible alternative for everyday data processing, cleanup, and analysis.
- 1. Download WPS Office: Visit the official WPS website and download the free installer for your operating system.
- 2. Open your spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx files. All your data and formatting will be perfectly retained.
- 3. Clean up data manually: Utilize built-in standard tools like Remove Duplicates, Text to Columns, and wildcard Find/Replace to manually streamline your customer lists.
Frequently Asked Questions
What is the similarity threshold in Power Query?
The similarity threshold is a numeric value between 0.00 and 1.00 that determines how strictly text must match. A threshold of 1.00 requires an exact, perfect match, while lower values (like 0.70) allow for typos, abbreviations, and missing words.
Can I use standard Excel formulas to group similar names?
Standard formulas like VLOOKUP or XLOOKUP generally require exact matches or rely on simple wildcards. They cannot automatically group text strings with unpredictable variations like different LLC suffixes without relying on complex, nested helper columns.
Why are completely different companies being grouped together?
Fuzzy matching algorithms calculate character similarity mathematically. If the names of two different companies are structurally very similar (e.g., 'Alpha Inc' and 'Alpha LLC'), a low similarity threshold might incorrectly group them. Increasing the threshold closer to 1.00 resolves this.
