Find Duplicates – Documentation

Find Duplicates tool iconGlossary Search tool allows you to search Excel glossaries from within any application and display entry information in a structured way similar to dictionary applications.

Detailed information about Find Duplicates tool

How Find Duplicates tool can help you in your work

If you have a glossary of terms in an Excel spreadsheet, you may have to remove duplicates from time to time. Find Duplicates is the best tool for the job: it searches a selected range for duplicates (i.e. rows whose values are identical to other rows) and highlights them or deletes their contents.

Where to find the tool

To run Find Duplicates tool, click its button on TransTools ribbon in Excel:

Find Duplicates tool button on the ribbon

Using Find Duplicates tool

The tool searches ONLY the selected range for rows which are identical to the row above. Depending on the selected options, it either highlights the cells within the selection (by changing their background color) or removes their contents.

Use it as follows:

  1. Open the Excel spreadsheet from which duplicates should be removed.
  2. [Optional] Sort by some additional columns if you need advanced duplicate removal capabilities (e.g., when you are merging two glossaries together and want records from one glossary to have priority over records from another glossary). Show how.
  3. Select columns which should be checked for duplicates.

    Example 1: You need to clean a big glossary file. The glossary file contains two entry columns (1 column for English, 1 column for German) and extra columns such as Description, Subject, etc. You would like to keep entries that are identical in English, but are different in German, or vice versa (i.e. different meanings of the same word or homonyms). In order to accomplish the task, select both entry columns (i.e. English and German).

    Example 2: You have compiled a single glossary file from many small glossaries provided by your fellow translators. You would like to put different meanings of the same word into the same column separated by ";". Select only one column where duplicates must be found and select "Highlight using Yellow background".

  4. Run Find Duplicates tool

    The command's progress will be shown in Microsoft Excel status bar at the bottom of the window (50% complete).

  5. If you used Clear duplicated cells option, re-sort the table by the first column (use guidelines in item 2) in order to shift all empty cells to either the start or end of the table. Delete these rows.

Options

Screenshot: Find Duplicates dialogue

  1. Highlight using Yellow background: duplicates will be highlighted (i.e. the background color of respective cells will be set to 'Yellow'.
  2. Clear duplicated cells: duplicate cells will be cleared (i.e. cell contents will be removed). This allows you to reorder the glossary so that all rows with empty cells shift to the top or bottom of the worksheet (where the rows can be removed easily).

Click OK to complete the operation. Click Cancel to close the dialogue.

Notes

  • For users of Excel 2007 and later: the new Remove Duplicates command in Excel 2007+ (available from Data Tools group of the Data tab) has similar functionality, but it cannot highlight duplicate cells.

Undo support

This command is undoable. However, due to restrictions in Microsoft Excel, as soon as you use this tool, you will be able to undo only the latest action, and everything done before that will be forgotten. Please bear this in mind when using TransTools for Microsoft Excel, and keep a backup copy of the spreadsheet that you are processing with TransTools.