You may need to delete duplicate domains when you do competitor research and your competitors have backlinks from the same domains or the same competitor has multiple backlinks from the same domain. This article will walk you through the whole process step-by-step so that you get a list of backlinks (one link per unique domain).
Extracting domain names from backlinks
So, say you have a list of your competitors’ backlinks in Google Sheets doc, but multiple backlinks are from the same domain.
Before we go any further, you want to freeze the row with column names in your sheet. That’s the top-most row. You just need to click the number of the row (to select the whole row), expand the “View” drop-down menu, go to “Freeze” and select the “1 row” option. Now if you scroll down your list, the first row will stay fixed in place and you’ll see the name of all the rows at all times.
The next thing you want to do is make the column with the backlinks (column E) the first one in your doc. It’ll just be easier to delete duplicate domains if you structure your sheet that way.
Now you need to create a new column to the right of the first column. In order to do that, you need to click the triangle icon in column A (the first column).
And now you just need to select the “Insert 1 right” option.
Having that done, you need to adjust the width of the new row and insert the following chunk of code right in the first cell of the new column:
=IF(ISERROR(FIND("//www.",A2)), MID(A2,FIND(":",A2,4)+3,FIND("/",A2,9)-FIND(":",A2,4)-3), MID(A2,FIND(":",A2,4)+7,FIND("/",A2,9)-FIND(":",A2,4)-7))
That will show the domain of the page (URL) in column A
Now you need to copy the content of the cell where you just pasted the code. Make sure that you don’t double-click the cell before you copy it.
Now you need to select the next cell in the column.
Now scroll down to the very bottom cell on the same column, hold down “Shift” and select the last cell (in this particular sheet the row number is 16850) so that you have a selected range that looks as you can see below.
Now expand the “Paste special” option and select “Paste formula only”.
That done, you’ll have a column of domains for each URL from column A. That will make it really obvious which rows you need to get rid of in order to delete duplicate domains.
Now you can just use the Remove Duplicates add-on to remove duplicates in column B. I’ll explain how exactly you can do that below.
Delete Duplicate Domains in Google Sheets
In order to delete duplicate domains, you need to do the following.
First off, you need to install the Remove Duplicates add-on for Google Sheets. Once the add-on has been installed, you need to select the whole column that you want to check for duplicates.
Now you need to expand the “Add-ons’ drop-down menu.
Select “Remove Duplicates” and then opt for “Find duplicates or uniques”
At this point, you just need to click “Next”.
If your list has 10,000+ entries, you’ll be prompted with the following message (you just want to click “Ok”).
Just make sure that the “Duplicates” checkbox is ticked (it should be ticked automatically).
And hit the “Next” button.
If your spreadsheet has a header just like my sheet, you want to keep this setting as is. I previously showed how to create a header by freezing row 1 (see above).
After that click “Next” again.
That done, you need to select the “Add a status column” option.
And finally, click the “Finish” button.
Now you should be seeing this message.
If your list of backlinks is huge, it may take a few minutes to perform the magic. Once the work is complete, you will see a message saying how many duplicate values have been found and marked in the status column (the “Duplicates note” column).
Now you can click the “Close” button and sort the brand new “Duplicates note” column in A-Z order.
At this point, you should have a sorted list of duplicate domains that you need to delete (just remove all the rows with the “Duplicate” value in the “Duplicates note” column.
Video on How to Delete Duplicate Domains
If you’re a video learner, here’s a video for you. I’ll walk you through the whole process of getting rid of duplicate domains in your list. Enjoy!
Useful Links
10 Google Sheets Formulas Every SEO Should Know
Conclusion
You’ve just learned a really neat skill, because – as an SEO guy – you may need to delete duplicate domains for all sorts of reasons. You may need to apply the skill when you need a list of unique domains from one competitor or from multiple competitors, etc. The sky’s the limit! One more cool thing is that you can use the same approach even if you use Microsoft Excel.
If you have any questions, feel free to let me know in the comments.
