Removing Duplicate Records

You can improve the quality of your data by identifying and removing duplicate data records.

The process to remove duplicate data involves the following actions:

  • Select match policies

    Depending on the data types in your worksheet, the application will suggest matching columns to identify duplicate records. In other words, if the values in these columns are equal, they are potential duplicate records. For example, if the Person and Phone values are the same, or if Person and Address values are the same.

    You can add other column combinations or delete column combinations in your match policies list.

  • Select survival rule

    After you have selected columns by which to identify duplicate records, you select a survival rule to determine which record in each group remains in your worksheet and the rest are deleted. You can choose one of the following rules:

    Survival Rule

    When to Use

    Description

    Example

    Length

    Column values in your worksheet have short values (such as abbreviations, acronyms, nicknames, and so forth) for the formal value which is usually longer

    You can select one or more columns for this rule. For each group of duplicate records, the record with the longest value in the specified columns is selected as the survivor.

    If you select the Length survival rule for first name, and one duplicate record group contains the values “Becky” and “Rebecca”, the record that contains “Rebecca” will be the survivor.

    Most Recent

    Your worksheet contains a date column

    For each group of duplicate records, the record with the most recent date is selected as the survivor.

    If you select the Most Recent survival rule for a column such as Last Updated, the latest record will be the survivor.

    Priority of Sources

    Your worksheet contains a column that identifies the source of the record

    The user interface displays the values in this column, and you can reorder the values to put them in priority order. The record that contains the highest priority value in the group of duplicate records is the survivor.

    Note
    You must use Chrome, Safari, or Internet Explorer as your web browser to display the values in this column.
    Your worksheet combines data from two different worksheets:

    1. Represents the master data and has a column with the value of “M” in every record.

    2. The other represents the delta data and has a column by the same name with the value of “D” in every record.

    If you select the Source survival rule for this source identification column, you can order the “M” value first so that when duplicate records exist in master and delta, the record from the master source is the survivor.

  • Set strictness rule for how closely records need to match in order to be considered duplicates.

    The default is 50% which is a best practice to balance between identifying fuzzy matches and not many false positives. You can also set other match settings such as the following:

    Match Setting

    Records match when the selected column…

    123456789 matches [blank]

    contains a value in one record and is empty in the other record

    [blank] matches [blank]

    is empty in both records

    Northwest matches NW

    contains an abbreviated word in one record and spelled out words in the other record

    Center matches Centre

    contains values that differ by a transposition of two characters