Clearing Duplicate Values in Excel

If you ever need to remove duplicate values from large sets of data in Excel, either for translation purposes or data preparation, download ASAP Utilities (free for home use), go to the “Range” section and select options 20 to 22:

2018-02-11_10-31-02

I personally prefer option 20, since it leaves 1 original value present and I can then clear the empty rows under section “Columns and Rows”, option 12.

Advertisements

A veritable Swiss army knife

Our chronic resistance to explore new tools in favor of something broken that sort-of-works is well documented. Using text editors for basic manipulation is no exception. My guesstimate is that the anachronic Notepad for Windows, which saw little to no change since 1985, is probably still the most popular text editor out there. victorinox_swissflash_1

After being enamored with Notepad++ for a few years, I’ve eventually decided to give EditPad Pro a spin, after a colleague of mine repeatedly extolled the benefits of the tool, which is not free by the way, but comes with a fully functional 30-day trial, after which you’ll still be able to use it, albeit in a pared-down mode.

The tool has a lot going for it, not least performance. EditPad Pro is the only editor I’ve tried which is capable of handling very large volumes of data (say those large TMXs from the European Commission) without giving up the ghost in the process.

I’ll be covering the many use cases for this tool in future posts but, for starters, here’s a great thing EditPad Pro can do for you if you’re an L10n Project Manager or just someone who needs to manipulate a large set of text files (including mark-up files or development scripts) in bulk:

2018-02-04_16-12-26

Let’s say you want to make a replacement across hundreds (or thousands) of text files. You can simply drag them into the tool, optionally hit the loupe key to see your changes highlighted, and click “Files” + “Projects” in the search bar at the bottom. Then click “Ctrl+ Alt + F3” to implement your change across all open files.

To complete the magic, go to “File” and hit Save All

2018-02-04_16-23-06

Even on a large set of over 1,000 files, this operation took less than 5 seconds. Not bad for a tool just under 40 euros.