* Logo
* Translator Tools
*
* Motto
* *
TransTools suite Other software Knowledge base Feedback Back to Index | Site map
*
*
*
*
**
**
**
**
***
***
***
**
*
*
*
*
*
*
Also on:

TransTools on Twitter TransTools on Google Plus TransTools on LinkedIn TransTools on Scoop.It!
*

Find duplicates

Excel
Category: Glossaries Back to Glossary Manager

Undo Undo support - YES. See general Excel undo notes.

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.

How it works?

The command 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.

How to use?

  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

    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.

Developed by Stanislav Okhvat, 2007–2017

Microsoft Word®, Excel®, PowerPoint® and Visio® are registered trademarks of Microsoft Corporation.
Autocad© is copyright of Autodesk, Inc.
SDL Trados® (including SDL Trados Studio, Trados Workbench, TagEditor and Microsoft Word Addin) is a registered trademark of SDL plc.
memoQ is copyright of Kilgray Translation Technologies.
Wordfast© is copyright of Yves Champollion.

Software disclaimer