Find Near-Duplicate Records in Excel with Fuzzy Matching

# Fuzzy Duplicate Cleanup for Your Customer List

**Recommendation:** Use **Power Query's Cluster Values + Merge with Fuzzy Matching** feature. It's built into Microsoft 365, handles 8,000 rows easily, and catches variations like "Robert/Bob" and "Acme Inc./Acme, Inc" without add-ins. Use a **similarity threshold of 0.80** for names (catches nicknames) and **0.85–0.90** for companies.

---

## Step-by-Step Walkthrough

**1. Load your data into Power Query**
- Select any cell in A1:D8000
- Go to **Data > Get & Transform Data > From Table/Range**
- Confirm headers, click OK

**2. Normalize text first (critical for accuracy)**
- Select the Name, Company, and Email columns (Ctrl+click)
- Go to **Transform > Format > lowercase**
- Then **Transform > Format > Trim**
- Then **Transform > Format > Clean** (removes hidden characters)

**3. Cluster similar values to create a grouping key**
- Select the **Company** column
- Go to **Add Column > From Text > Cluster Values**
- Name the new column "Company_Cleaned"
- Click **Fuzzy cluster options** > set **Similarity threshold to 0.85**
- Repeat for the **Name** column with threshold **0.80**

*Example:* "Acme Inc." and "Acme, Inc" will both cluster to one value. "Robert Smith" and "Bob Smith" at the same company will cluster together.

**4. Find duplicates using fuzzy Group By**
- Go to **Home > Group By > Advanced**
- Group by **Name_Cleaned** and **Company_Cleaned**
- Check **Use fuzzy matching**
- Set **Similarity threshold: 0.85**
- Add aggregations: Count Rows, and "All Rows" (to preserve original data)
- Click OK

**5. Expand and review**
- Click the expand icon on the "All Rows" column
- Select all original columns
- Filter the Count column for **> 1** to see only duplicate groups

**6. Load results for review**
- Go to **Home > Close & Load To > Table > New Worksheet**

**7. Review before deleting**
- Sort by Company_Cleaned so duplicate groups sit together
- Add a "Keep" column and mark the record with the most complete data (full email, phone, fewer blanks)
- Use `=IF(COUNTIF(...)>1,"REVIEW","")` to flag clusters if needed

---

## Final Tip

**Never delete directly from Power Query output.** Export the flagged duplicates to a separate sheet, manually verify 20–30 random matches first (especially near the 0.80 threshold — that's where false positives hide), then use the "Keep" column to filter and delete losers. For merging data from duplicates (e.g., one row has phone, another has email), use the expanded table to copy missing fields into the surviving record before deletion.

# Fuzzy Duplicate Resolution Walkthrough

## Recommendation
Use **Power Query's Merge/Cluster Values feature** (built into Microsoft 365). It's fastest for 8,000 rows, requires no add-ins, and lets you review matches before committing.

---

## Step-by-Step Process

### 1. Load Your Data into Power Query
- Select your data (A1:D8001, including headers)
- **Data > Get & Transform > From Table/Range**
- Click **OK** in the dialog

### 2. Access the Cluster & Merge Feature
- In Power Query Editor, select the **Name** column (your primary fuzzy-match field)
- **Home > Cluster & Merge** (or **Transform > Cluster & Merge** if unavailable in your version)
- A clustering dialog opens

### 3. Configure Clustering
- **Similarity Threshold:** Set to **0.85** (85% similarity)
  - 0.90 = stricter (misses "Bob" ↔ "Robert")
  - 0.80 = looser (may over-merge)
- **Language:** Select your data's language
- **Algorithm:** Keep default (Levenshtein distance)

### 4. Review Proposed Matches
Power Query shows a table of similar values grouped together:
- *Example:* "Robert Smith" | "Bob Smith" | "R. Smith" grouped as one cluster
- Review each cluster in the preview pane
- **Uncheck any incorrect groupings** to exclude them
- Click **OK** to apply

### 5. Select the Replacement Value
For each cluster, choose which variant to keep (e.g., "Robert Smith" replaces all variants). Select your preferred version from each group.

### 6. Complete the Merge
- Power Query replaces all variants with your chosen value
- Click **Close & Load** to return the cleaned data to Excel

### 7. Repeat for Other Columns (Optional)
- If needed, repeat steps 2–6 for **Company** (e.g., "Acme Inc." vs "Acme, Inc.")
- Phone and Email typically need exact-match deduplication instead

### 8. Remove True Duplicates
- Select all data
- **Data > Remove Duplicates** (or **Data > Data Tools > Remove Duplicates**)
- Check columns A–D
- Click **OK**

---

## Example Match Results
| Threshold | Matches | Notes |
|-----------|---------|-------|
| 0.85 | "Robert Smith" ↔ "Bob Smith" | Same company, similar enough |
| 0.85 | "Acme Inc." ↔ "Acme, Inc." | Punctuation variation |
| 0.85 | "John" ↔ "Jon" | ❌ Won't match (too different) |

---

## Final Review Tip
**Before deleting:** Export cleaned data to a new sheet, compare row counts with the original (note how many rows merged), and spot-check 10 random clusters manually. Keep a backup of your original file.

For your situation on Microsoft 365, the best built-in tool is **Power Query's Fuzzy Merge**. It's powerful, requires no extra installation, and can match records based on the similarity of text in multiple columns like Name and Company.

Here is the step-by-step walkthrough:

1.  **Load Data into Power Query:**
    *   First, format your customer list as an official Excel Table (**Home > Format as Table**).
    *   Click anywhere inside your table, then go to the menu **Data > Get & Transform Data > From Table/Range**. This opens the Power Query Editor.

2.  **Add a Unique ID Column:**
    *   In the Power Query Editor, go to the **Add Column** tab and click **Index Column**. This adds a unique ID to each row, which is essential for identifying the original records.

3.  **Perform the Fuzzy Merge:**
    *   On the **Home** tab, click the dropdown for **Merge Queries** and select **Merge Queries as New**. This creates a separate results table, protecting your original data.
    *   In the Merge window, select your table name in both the top and bottom dropdowns to match the list against itself.

4.  **Configure the Fuzzy Match:**
    *   Select the columns you want to match on. Hold the **Ctrl** key and click the **Company** column header, then the **Name** column header in *both* tables. The order must be the same.
    *   Check the box at the bottom: **Use fuzzy matching to perform the merge**.
    *   Set the **Similarity threshold** to **0.85**. This is a good starting point (1.0 is an exact match).
    *   *Example:* A threshold of 0.85 would easily match "Acme Inc." with "Acme, Inc." and is likely to catch variations like "Robert Smith" and "Bob Smith".
    *   Click **OK**.

5.  **Expand and Review the Matches:**
    *   A new column appears with expandable table data. Click the expand icon (two arrows) in its header

**Recommendation: Use Power Query's fuzzy merge (built into M365) – ideal for 8k rows, handles multi-column fuzzy matching like Name+Company without add-ins.**

1. Select your data (A1:D8001 incl. headers), go to **Data > From Table/Range** to load into Power Query Editor.

2. In Power Query, right-click your table > **Duplicate** (creates Table2 copy). Close & Load Table1 only.

3. Reopen Table1 in PQ: **Home > Transform Data**. Go to **Home > Merge Queries > Merge Queries as New**.

4. Select Name (A) in Table1, Ctrl+click Company (C), then Table2's Name+Company. Check **Use fuzzy matching**, set **Similarity threshold: 0.85** (catches 'Robert Smith' + 'Acme Inc.' vs 'Bob Smith' + 'Acme, Inc.').

5. Expand merged column (click icon), select only matching rows (e.g., Table2.Name, Table2.Email). Filter to keep fuzzy matches (non-null).

6. **Home > Close & Load** to sheet. Review matches (e.g., side-by-side columns highlight fuzzy pairs).

7. Manually merge data (e.g., prioritize Email/Phone), flag/delete duplicates via **Data > Remove Duplicates** or filter.

**Example match at 0.85:** 'Robert Smith' + 'Acme Inc.' fuzzy-matches 'Bob Smith' + 'Acme, Inc.' (name ~0.82 sim, company ~0.95).

**Tip:** Sort/filter results by similarity score column (add via Custom column: `Similarity([Merged])`), spot-check 10-20 pairs before bulk delete to avoid false positives.

(248 words)