REMOVING DUPLICATES BY TEXT SIMILARITY

Introduction to Removing Duplicates

To remove duplicates from a single column using Flookup, go to Extensions > Flookup Data Wrangler > Remove duplicates in your spreadsheet menu.

Removing Duplicates by Percentage or Sound Similarity

1. Select the function to run

   • Click the menu item labelled "By percentage" or "By sound", depending on what you want to do.

2. Select the mode to run

   • Select the mode you want this function to run via the first drop-down menu. Your choices are:

     1. Keep first unique value

     2. Keep last unique value

3. Select the text entries to analyse

   • Select text entries of one or more columns in your spreadsheet. Please note that if you select range A2:D500, for example, and duplicates are identified on row C10, C20 and C50, then A10:D10, A20:D20 and A50:D50 will be removed.

4. Index the selected data

   • Click "Map columns in selection" in order to index the current columns in your selection.

5. Specify the column of data to analyse

   • Enter the Left_column index. If no user input is made, then the first column of the selected range will be analysed.

6. Enter the level of similarity

   • This step is only necessary when running "By percentage". Enter the Threshold value. If no user input is made, only exact matches will be deleted. Increasing this value will make this function recognise only close matches as duplicates and lowering this value will make it treat even loose matches entries as duplicates.

7. Remove Duplicates

   • Click the "Remove duplicates" button.

-----

Notes On Removing Data Based on a Single Column

• • The Left_column value is the only column that will be analysed in this mode. This can be any integer representing a single column inside your selection.

  • If you are running "By percentage", then duplicates will be text entries within Left_column that have a level of similarity that is higher than or equal to the Threshold value.

  • If this function finishes running or times out, a message will be displayed indicating how many rows have been processed up to that point.

How to Remove Duplicates Across Two Different Columns

1. Select the function to run

   • Click the menu item labelled "By percentage" or "By sound", depending on what you want to do.

2. Select the mode to run

   • Select the mode you want this function to run via the first drop-down menu i.e.

     1. Keep first unique value

     2. Keep last unique value

3. Select the comparison mode

   • Select the second drop-down option labelled Compare two different columns.

4. Select the data to compare

   • Select text entries of two or more columns in your spreadsheet. This also determines the number of columns that will be deleted for each row that contains duplicates.

5. Index the selected data

   • Click "Map columns in selection" in order to index the columns in your current selection.

6. Specify the column indexes to analyse

   • Specify your Left_column and Right_column index. These are the two columns that will be compared to each other.

7. Set the level of similarity

   • If you selected "By percentage" in step #1, adjust the Threshold value to match your needs. Otherwise, skip this step. Increasing this value will make this function consider only close matches as duplicates and lowering this value will make it treat even loosely matched entries as duplicates.

8. Remove duplicates

   • Click "Remove duplicates".

-----

Notes On Removing Data Across Two Columns

• • • Before removing duplicates across different columns, it is advisable to remove any duplicates within Left-column first.

    • Duplicates are values in Left_column that exist in Right_column and any row with a duplicate will be deleted.

    • Duplicates are text entries that have a level of similarity that is higher than or equal to Threshold between them.

How to Remove Duplicate Rows

To remove duplicate rows by considering all columns within the selection e.g. comparing A2:E2 to A3:E3 for similarity, simply follow these steps:

1. Select the function to run

   • Click the menu item labelled "By percentage" or "By sound", depending on what you want to do.

2. Select the mode to run

   • Select the mode you want this function to run via the first drop-down menu, namely:

     1. Keep first identified duplicate value

     2. Keep last identified duplicate value

3. Select the comparison mode

   • Select the second drop-down option labelled Compare data in selection by row.

4. Select the data to compare

   • Select the data range of two or more columns to be analysed for duplicates. This also defines the number of columns that will be deleted for each row that is considered a duplicate.

5. Index the selected data

   • Click "Map columns in selection" in order to index the current columns in your selection.

6. Set the level of similarity

   • If you selected "By percentage" in step #1, adjust the Threshold value to match your needs. Otherwise, skip this step. Increasing this value will make this function treat only close matches as duplicates and lowering this value will make it treat even loosely matched entries as duplicates.

7. Remove duplicates

   • Click "Remove duplicates".

How to Remove Duplicates of Data in a Single Cell

To remove duplicates from a single column e.g. A1:A1000 based on matches from a single cell e.g. A250, follow these steps:

1. 1. Click the menu item labelled "By percentage" or "By sound".

   2. Select the second drop-down option labelled Remove duplicates by cell value.

   3. Select the data range of one or more columns comprising the data to be analysed for duplicates and click "Map columns in selection".

   4. Click any cell containing the content whose duplicates you would like to remove and click "Grab selected cell".

   5. Change the "Left_column" value to specify the column index from which you would like to remove duplicates.

   6. If you are running "By percentage", adjust the Threshold value to match your needs. Otherwise, skip this step.

   7. Click "Remove duplicates".

How to Roll Up Data from Duplicate Rows

To combine or concatenate duplicate rows by considering all columns within the selection e.g. combining A2:E2 , A3:E3 and A4:E4, simply follow these steps:

1. 1. Click the menu item labelled "By percentage", depending on what you want to do.

   2. Select the second drop-down option labelled Roll up data in selection by row.

   3. Select the data range of two or more columns to be analysed for duplicates.

   4. Click "Map columns in selection" in order to index the current columns in your selection.

   5. If you are running "By percentage", adjust the Threshold value to match your needs. Otherwise, skip this step.

   6. Click "Remove duplicates".

How to Extract Unique Values

1. Select the function to run

   • Click the menu item labelled "By percentage" or "By sound", depending on what you want to do.

2. Select the mode to run

   • Select the mode you want this function to run via the first drop-down menu, namely:

     1. Keep first identified duplicate value

     2. Keep last identified duplicate value

3. Select the comparison mode

   • Select the second drop-down option labelled Compare data in selection by row.

4. Select the data to compare

   • Select the data range of two or more columns to be analysed for duplicates. This also defines the number of columns that will be deleted for each row that is considered a duplicate.

5. Index the selected data

   • Click "Map columns in selection" in order to index the current columns in your selection.

6. Set the level of similarity

   • If you selected "By percentage" in step #1, adjust the Threshold value to match your needs. Otherwise, skip this step. This value will determine how strict this function will be at identifying duplicates. Increasing the value will make the function recognise only close matches as duplicates and, therefore, filter fewer text entries. Conversely, lowering the value will make it treat even loosely matched entries as duplicates and, as a result, it will filter out more text entries.

7. Remove duplicates

   • Click "Remove duplicates".

For the Visual Learners

Explore Further

• Highlighting Duplicates by Text Similarity

• Match and Merge Data by Text Similarity