Find Near Duplicate Keywords in Excel

Why should we find the keywords that are almost same

Understanding Keyword Mapping and its Usability: Concisely

Keywords, phrases or topics are the foundation of SEO. Using multiple keywords and/or related keywords/terms within a web page allows you to up-skill your SEO game. Keyword Mapping is the process of assigning relevant keywords to their respective web pages. It helps in determining which keywords to use and how relevant they would be for a particular page on your website. This way, you will be able to make specific on-page SEO recommendations to make the page more relevant. It avoids duplicate content. Without keyword mapping, your SEO won't be effective.

Now, here comes the problem. Excel is able to find the exact duplicate values (in the same order and sequence), but it won't find reverse, misspelled or mixed words of the same phrases or keywords.

For example, You might have keywords listed as new york personal lawyer for injury and personal injury lawyer new york. Or difference may be in city name like new york or nyc. Each time they are slightly different. See examples below

• new york personal lawyer for injury
• personal injury lawyer new york
• new york personal injury lawyer
• personal injury lawyer nyc
• nyc personal injury lawyer

Excel Formula that will help you to find the near-duplicate keywords

To solve the problem, we will use a combination of various excel functions SEARCH and AND. The SEARCH function returns the location of one text string inside another. Unlike FIND, SEARCH is a not a case-sensitive function. The AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE.

Step By Step Process in Finding Duplicate Keywords in Excel: Concisely

SEARCH Function Example

=SEARCH("NEW YORK",A2)

The function will find the location of new york in the string 'new york personal lawyer for injury', and return the position 24. The function will return the number, if it finds the text, else returns the #VALUE. See the example below.

=SEARCH("NYC",A2)

The function won't find NYC in the string so it will return #VALUE. To escape from this, we use ISNUMBER with SEARCH. The function will return either TRUE or FALSE.

=ISNUMBER(SEARCH("NEW YORK",A2)) will return TRUE.

=ISNUMBER(SEARCH("NYC",A2)) will return FALSE.

To search the multiple strings in a single cell, we will combine another excel function AND.

=AND(SEARCH("new york",A2),SEARCH("personal",A2))

The above function will find both words "new york" and personal" in Cell A2. If any of them is missing, the formula will return #VALUE. To make the cell value more sophisticated, use ISERROR.

=IFERROR(AND(SEARCH("new york",A2),SEARCH("personal",A2),SEARCH("injury",A2),SEARCH("lawyer",A2)),"No Match")

The formula will see if all the words mentioned in the formula are in the cell. If the formula failed to find any of these keywords in the cell, it will return 'No Match.' Though a problem still lies with us, i.e we should look for either new york or NYC to find the nearest duplicate keywords. To solve this problem, we will use an ARRAY list in the formula with the OR function. Below function will look for either "NEW YORK" or "NYC" in the cell A2.

=OR(ISNUMBER(SEARCH({"new york","nyc"},A2)))

FINALLY, WE WILL COMBINE ALL THE ABOVE FUNCTION TO MAKE A SOLID EXCEL FORMULA TO FIND NEAREST DUPLICATE KEYWORD

=IFERROR(AND(OR(ISNUMBER(SEARCH({"new york","nyc"},A2))),SEARCH("personal",A2),SEARCH("injury",A2),SEARCH("lawyer",A2)),"No Match")

ENJOY :)

=IF(AND(OR(ISNUMBER(SEARCH({"new york","nyc"},A2))),SEARCH("personal",A2),SEARCH("injury",A2),SEARCH("lawyer",A2)),CHAR(252),CHAR(251))