How to find similar duplicates

Answer

To find similar duplicates in Excel, you can use multiple methods depending on whether you want exact duplicates or near matches. Excel offers built-in tools and formulas that simplify the process.

Method 1: Using Conditional Formatting

Conditional Formatting highlights cells that contain duplicates.

1. Select the range of cells you want to check for duplicates.

2. Go to the Home tab.

3. Click Conditional Formatting → Highlight Cells Rules → Duplicate Values.

4. In the pop-up window, select the formatting style (e.g., red fill with dark red text).

5. Click OK.

The duplicates will be highlighted in the selected style.

Variation for Mac: The steps are the same, but the Conditional Formatting menu may appear under Format → Conditional Formatting in older versions.

Method 2: Using the COUNTIF Formula

The COUNTIF function can detect duplicates by counting occurrences of each value in a range.

1. Suppose your data is in column A (A2:A100). In cell B2, enter: 

   =COUNTIF($A$2:$A$100, A2)

2. Press Enter.

3. Drag the formula down through column B.

• Any value greater than 1 indicates a duplicate.

• You can filter or sort column B to see duplicates quickly.

Example: If A2 = Apple and it appears three times in the range, B2 will show 3.

Method 3: Using TEXTJOIN and FIND for Similar (Near-Match) Duplicates

To find values that are similar but not the same (like "Apple Inc." and "Apple Incorporated"), you can use formulas combining TEXTJOIN, SEARCH, or FIND.

1. Suppose your data is in column A.

2. Use a helper column B to identify similarity. For example: 

   =IF(SUMPRODUCT(--ISNUMBER(SEARCH("Apple", A$2:A$100)))>1,"Similar","")

3. This flags cells containing the word "Apple" more than once.

4. Replace "Apple" with any key string you want to check for similarity.

This method is useful for partial matches or entries with minor differences.

Method 4: Using Excel’s Remove Duplicates Tool (for Exact Matches)

1. Select the range of data.

2. Go to the Data tab.

3. Click Remove Duplicates.

4. Select the columns to check.

5. Click OK.

Excel removes duplicates, leaving unique entries. You can use this to create a clean list and compare it with the original to find duplicates.

Method 5: Using Power Query for Advanced Duplicate Detection

Power Query is useful for detecting fuzzy duplicates or near matches.

1. Go to Data → Get & Transform → From Table/Range.

2. Load the range into Power Query.

3. Select the column to analyze.

4. Go to Home → Remove Rows → Remove Duplicates (for exact duplicates) or Transform → Fuzzy Merge (for similar duplicates).

5. Adjust the similarity threshold for near matches (e.g., 80%).

This method identifies both exact and approximate duplicates efficiently in large datasets.

Tips for Handling Duplicates

1. Always create a backup of your data before removing duplicates.

2. Use helper columns with COUNTIF or IF statements to avoid accidental data loss.

3. For text variations (e.g., spacing, capitalization), use TRIM and UPPER functions to standardize before checking for duplicates: 

   =UPPER(TRIM(A2))

These methods allow you to identify both exact and similar duplicates effectively, whether your dataset is small or large.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report