Implicit duplicates and their removal are one of the most important tasks when working with text content. SEO and PPC specialists, webmasters and content managers deal with them all the time.
Keyword lists, search queries, product and service catalogs, lists of pages and URLs often contain so-called implicit, or hidden, duplicates: cells whose content a human sees as a repeat of something already in the table, but a computer doesn’t, because of minimal, sometimes invisible differences. The built-in Remove Duplicates and Conditional Formatting don’t see them either.
Let’s take a closer look.
Why implicit duplicates appear
If you analyze why a computer sees a difference between cells, you can name several reasons. Differences that don’t matter to a human but matter to a computer:
- a different word order: “buy red sofa” and “red sofa buy”;
- extra spaces at the beginning, at the end or between words;
- punctuation left in the cells: “sofa, red” and “sofa red”;
- different letter case: “Red Sofa” and “red sofa” (COUNTIF ignores case, but some tools don’t);
- look-alike letters from another alphabet, for example a Cyrillic “а” instead of the Latin “a” in data copied from the web;
- different word forms: “sofa” and “sofas”, “buy” and “buying”;
- in SEO and PPC, so-called stop words: “sofa for living room” and “sofa living room”.
| Phrase 1 | Phrase 2 | Difference |
|---|---|---|
| buy red sofa | red sofa buy | word order |
| red sofa | red sofa | spaces |
| sofa, red | sofa red | punctuation |
| red sofas | red sofa | word form |
How to normalize phrases before comparing
For each type of difference there is a way to remove it:
- sorting the words inside a cell solves the different word order;
- removing all characters except letters, digits and spaces removes differences in punctuation;
- removing extra spaces handles spaces;
- lemmatization brings words to their base form.
After removing all differences, you can find duplicates in the column and analyze which phrases implicitly duplicate each other. You get three columns: the source phrases, the normalized phrases and their duplicate status.
In Excel 365, you can build a normalized key with one formula: lowercase, extra spaces removed, words sorted alphabetically:
=TEXTJOIN(" ",TRUE,SORT(TEXTSPLIT(LOWER(TRIM(A2))," "),,,TRUE))
“Red sofa BUY” and “buy red sofa” both turn into “buy red sofa”. Put this formula in column B and mark the repeats in column C with COUNTIF:
=COUNTIF($B$2:B2,B2)>1
TRUE means the phrase in this row is an implicit duplicate of a phrase above. It works, but it takes two helper columns, a formula only Excel 365 understands, and punctuation and word forms are still not handled.
Remove implicit duplicates in 1 click
How do you remove duplicate phrases whose words differ only in order? The tool is in the !SEMTools menu DELETE → Cells → Duplicates within a range. It works like the regular removing of duplicates without shifting rows: it simply clears the cell if it’s an implicit duplicate of one of the cells above. No helper columns, no formulas:
Removing implicit duplicates with different word forms
In PPC and SEO, when working on a keyword list, it’s important to remove duplicates that differ not only in word order, but also in word forms. In the example above, such duplicates are not removed.
Lemmatization helps here. So that you don’t have to create an extra column and lemmatize it, the duplicates menu also has an item that removes duplicate words and phrases with lemmatization. The source phrases don’t change: lemmatization happens in the background and is used only to compare the phrases.
Removing implicit duplicates of another column
When you just need to compare two ranges and remove the duplicates from the first one, the task isn’t hard. You can do it even with Conditional Formatting or the VLOOKUP function. But if the second column contains implicit duplicates and you want to remove them too, you can’t do without programming skills.
There is a simple solution, though: the matching !SEMTools procedure. In a couple of clicks, you compare the ranges and remove the implicit duplicates, without programmers or searching for ready-made solutions on Excel forums:
As you can see, the implicitly duplicated cells of the first range were found in the second one and removed. The procedure is available in both the Pro and the Lite versions of the add-in.
Remove implicit duplicates with a different word order, extra spaces or other word forms right in place, without helper columns.
!SEMTools solves this and hundreds of other tasks in a couple of clicks!
Summary
I hope you found what you were looking for on this page and were able to get rid of the implicit duplicates in your data. The formula approach needs normalization in helper columns and still misses word forms. For keyword lists and catalogs, the !SEMTools procedures handle it right in place.
You may also be interested in how to remove duplicate cells and other ways to delete cells by condition.