When creating a high-quality semantic core for a large project with thousands of queries, it is important to eliminate duplicate keywords.
To quickly and accurately identify duplicate keywords, use a formula in Google Sheets that will highlight identical queries (or other values).
Setting up a Google spreadsheet to identify the same keywords
- Select a data range: Let’s say your list of keywords is in column B, starting in cell B2 and going to the end of the list.
- Using conditional formatting: We’ll use conditional formatting to highlight duplicate values.
- Formula to highlight duplicates:
- Select a range of data, for example, B2:B1000.
- Choose Format > Conditional formatting.
- In the window that opens, in the Format rules section, select Custom formula is.
- Enter the formula:
=COUNTIF($B$B:$B$1000,B2)>1 - Also an analog of the formula:
=AND(NOT(ISBLANK(B1)); COUNTIF($B$2:$B; "=" & B1) > 1)
The formula checks if the value in cell A2 (and all subsequent cells) occurs more than once in the specified range. If so, the cell is highlighted in a different color.
- Formatting settings: Specify the desired highlighting format, such as background or text color, so that duplicate values are easy to see.
- After applying the formula, the cells with “new keywords” and “missing keywords” will be highlighted because they appear more than once.
You need to choose where to remove the keyword repetition and the fill from the second keyword will disappear.
Using this formula allows you to quickly and accurately identify duplicate keywords in the semantic core of a project without spending additional effort and time.