How to find partial duplicates

Answer

Partial duplicates occur when cells contain similar information but are not identical. Excel provides multiple methods to identify these cases.

Method 1: Using the COUNTIF Function

The COUNTIF function counts the number of times a value appears in a range. This method helps identify partial matches with wildcards.

1. Select a blank column next to your data.

2. Enter the formula: 

   =COUNTIF(A:A, "*"&B1&"*")

   • A:A is the column where you want to check duplicates.

   • B1 is the cell containing the value you want to compare.

   • * acts as a wildcard to allow partial matches.

3. Press Enter and drag the formula down.

4. Any number greater than 0 indicates a partial duplicate.

Method 2: Using Conditional Formatting

Conditional formatting highlights cells that contain partial duplicates.

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

2. Go to Home → Conditional Formatting → New Rule.

3. Choose Use a formula to determine which cells to format.

4. Enter the formula: 

   =COUNTIF($A$1:$A$100,"*"&A1&"*")>1

   • $A$1:$A$100 is the range to check.

   • A1 is the first cell in the selected range.

5. Click Format, choose a highlight color, then click OK.

6. Partial duplicates in the range will be highlighted automatically.

Method 3: Using the SEARCH or FIND Functions

These functions locate specific text within a cell. Combine with ISNUMBER to flag matches.

1. In a blank column, enter: 

   =IF(ISNUMBER(SEARCH("part-of-text", A1)), "Partial Duplicate", "")

   • "part-of-text" is the substring to search for.

   • A1 is the cell being checked.

2. Press Enter and copy the formula down.

3. Cells containing the substring will display "Partial Duplicate".

Method 4: Using Power Query for Advanced Partial Matching

Power Query provides more control for large datasets.

1. Select your table and go to Data → Get & Transform Data → From Table/Range.

2. In Power Query, select the column to check.

3. Go to Add Column → Custom Column.

4. Use the formula:

   Text.Contains([Column1], "substring")

   • Returns TRUE for partial matches.

5. Load the data back to Excel, filtering TRUE values to see partial duplicates.

Tips to Improve Accuracy

1. Standardize text by using the TRIM and UPPER functions to remove spaces and match cases.

   =UPPER(TRIM(A1))

2. Remove punctuation with SUBSTITUTE if data contains special characters.

3. For large datasets, Power Query is more efficient than formulas.

Partial duplicates are best handled by combining wildcards, text functions, and conditional formatting. This ensures that variations in text do not hide duplicate information.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report