MATCH AND MERGE DATA BY TEXT SIMILARITY

Fuzzy Match by Percentage Similarity

1. Open the sidebar

   • Go to Extensions > Flookup Data Wrangler > Fuzzy match data > By percentage in your spreadsheet menu.

2. Select the mode to run

   • Return best match only v1: Returns the best match for every lookup value.

   • Return best match only v2: Returns the best match for every lookup value, but it is slightly faster than the previous mode.

   • Return best match and similarity: Returns the best match for every lookup value with its respective similarity.

   • Return best match and row index: Returns the best match for every lookup value with the row index in which it is located.

   • Merge data from rows with matches: Returns the selected "Secondary range" data from rows where a match is found. For example, if range B2:E1000 is selected and A2 matches a value in column C (e.g., C50), data from B50:E50 is returned.

   • Match entries on a word-level: Compares text entries on a word-level of similarity. It is suited for long text entries like sentences and paragraphs, and it returns the best match for every lookup value with its respective similarity.

3. Select the range containing the lookup value

   • Highlight a single column of data e.g. A1:A100 and click "Grab selected range" to read it into Primary range.

4. Select the range to compare with values in step 3 above

   • Highlight a separate range of one or more columns e.g. B1:D100 and click "Grab selected range" to read it into Secondary range.

5. Specify the "Secondary range" column to focus on for processing

   • Enter the Lookup_column index. This is the column in "Secondary range" to compare with values in "Primary range". Therefore index 1 will compare the first column in B1:D100, index 2 will compare the third column in B1:D100, etc.

6. Specify the "Secondary range" return column

   • Enter the Return_column index. This is the column in "Secondary range" from which matches are to be returned. Therefore index 1 will return values from the first column in B1:D100, index 2 will return values from the third column in B1:D100, etc.

7. Set the level of similarity

   • Enter the minimum percentage similarity between values in "Primary range" that match values in "Secondary range". The default value is 0.85.

8. Specify the output location

   • Click any empty cell where you would like your results to be displayed. This cell should be in any sheet inside that same spreadsheet you are working on.

9. Get fuzzy matches

   • Click the "Get fuzzy matches" button to finish.

-----

Notes About Fuzzy Matching Data

• Lookup_column takes only one column. If you select more than one column, only the left most column of that range will be analysed.

• In order for this function to return a match and its respective percentage similarity or its respective position in the Secondary range, you must leave two empty columns for the results to be displayed. If you forget to do this, your data will be overwritten.

• For better results from the matching entries on a word-level, be sure to correct any spelling errors as a preprocessing step. You can find the function under Tools > Spelling > Spell check. You also need to leave two empty columns to accommodate results in this mode.

Fuzzy Match by Sound Similarity

1. Open the Sidebar

   • Go to Extensions > Flookup Data Wrangler > Fuzzy match data > By sound in your spreadsheet menu.

2. Select the range containing the lookup value

   • Highlight a single column of data and click "Grab selected range" to read it into Primary range.

3. Select the range where the lookup is potentially located

   • Highlight a separate range of one or more columns and click "Grab selected range" to read it into Secondary range.

4. Specify the lookup column

   • Enter the index of the column in "Secondary range" to compare with values in "Primary range". Only the leftmost column will be analysed if more than one column is selected.

5. Specify the return column

   • Enter the index of the column in "Secondary range" from which you want values to be returned.

6. Specify output location

   • Click an empty cell within that same spreadsheet to mark the position where the results should be displayed.

7. Get fuzzy matches

   • Click the "Get fuzzy matches" button to finish.

For the Visual Learners

Explore Further

• Preprocess Data by Text Similarity

• Fuzzy Matching Algorithms Explained