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?
- Open the Excel spreadsheet from which duplicates should be removed.
- [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). .
- 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".
- Run Find Duplicates
The command's progress will be shown in Microsoft Excel status bar at the bottom of the window (50% complete).
- 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.
Highlight using Yellow background:
duplicates will be highlighted (i.e. the background color of respective cells will be set to 'Yellow
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).