This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlighted yellow and are converted into a hyperlink.
Automation and link building do not belong in the same sentence, or do they? Some tasks involved in great link building can be tedious. Why not make your life a little easier? Part of resource link building involves reaching out to a lot of sites and offering an asset with a link back to your client. Every once in awhile, a site will put up your link and never tell you. No problem, you can run a backlink check and see any new links your client has obtained. But how do you sort out the links that you obtained through your own efforts? Well you would take your list of domain URL’s that you have reached out to and dump them into an Excel file. Then you would download any referring domain backlinks and add these to your list of URL’s that you have reached out to. Then type ALT+F8 and hit Enter and your work is done (if you used my code). Over the years I have spent a lot of time learning Excel, and how to automate projects. If there is a task that I do regularly, I try and automate what I can. Time is money, efficiency is key. Of course, some things should never be automated. Part of great link building involves building relationships with other sites. You are much more likely to build a relationship as yourself versus an automated robot. Use this script to simplify your link checking and save some time. This code will take your list of outreached sites combined with your client backlinks and look to see if you have gained any links through your outreach efforts. This is done by looking for URL’s that are duplicated in your list, suggesting that a site you outreached is showing a backlink to your client as well. The code takes your list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlighted yellow and are converted into a hyperlink for ease of use. How to get the code into Excel. Open up an Excel workbook.
Select the “File” tab. Select “Options” at the bottom of the File menu. The Excel Options window will appear, select “Customize Ribbon”. Check the “Developer” box under Main Tabs. The Developer tab will now appear in all of your Excel workbooks. Select the “Developer” tab, then select “Visual Basic” on the very left. Alternatively, you can type ALT+F11. A new window will open up, Visual Basic Editor (VBE). In the Project Tree, right-click VBAProject (PERSONAL.XLSB). Select Insert, then Module. Copy the code in this article and paste into the Module window. Click the “Save” icon or type CTRL+S to save the code into your Personal Workbook. Close out the Visual Basic Editor. Populate Column “A” with your list of domain URL’s that you have already outreached. Note that these URL’s are domain level. It is important that all URL’s in this list are domain level, so that duplicate values can be found. Run a backlink report on the client in question. Type your client URL in the Site Explorer. Click “Referring domains” in the menu on the left. Click “Export” on the right side. A new window will popup, select the “Microsoft Excel” radio button and click “Start Export”. Open up the workbook you just downloaded. In the “B” column, all Fresh domain level backlinks will be listed. Click the “B2” cell and hold down CTRL+SHIFT+DOWN to select all the URL’s in the “B” column. Then hold down CTRL+C to copy the selected cells. Now that you have the backlinks copied, close the workbook. Open up the Excel workbook with your already outreached URL’s. Select the first empty cell in column “A” and paste your backlinks by holding CTRL+V. Hold down ALT+F8 to open up the Macro window. Select PERSONAL.XLSB!HighlightDuplicateLinks and click “Run”. A new window titled “Look for Duplicates” will open. Click OK. Once script is completed, (a few seconds depending on the amount of URL’s), click OK. If you have sites that you outreached that did indeed link back to your client, they will appear at the top of the list in yellow. Your work is done and you can easily see if you have any additional links to report to your client. Anytime you have a list of URL’s that you want to check, simply type ALT+F8 and click Run on the selected macro. Copy the code below:
Sub HighlightDuplicateLinks()'Takes URL's in Column A, sorts and highlights duplicate values.Dim dialogBoxDim finishBox dialogBox = MsgBox("Look for Duplicate Values in Column A?" & _ vbNewLine & vbNewLine & "Written by Joshua Snow" & _ vbNewLine & "firstname.lastname@example.org", vbOKCancel, "Look for Duplicates") If dialogBox = vbCancel Then Exit Sub End IfDim lastRow As LongDim duplicateFound As LongDim i As LongDim myWB As WorkbookSet myWB = ActiveWorkbookDim ws As WorksheetSet ws = myWB.ActiveSheetDim cell As Rangews.Range("A1").EntireRow.Insertws.Range("A1").Value = "URL List"lastRow = Cells(Rows.Count, "A").End(xlUp).RowFor i = 1 To lastRowIf Cells(i, 1) <> "" Then duplicateFound = WorksheetFunction.Match(Cells(i, 1), Range _ ("A1:A" & lastRow), 0)If i <> duplicateFound Then Cells(i, 1).Interior.Color = rgbYellowEnd IfEnd IfNext AutoFilterMode = False Range("A1").AutoFilterWith ws .AutoFilter.Sort.SortFields.Add(Range _ ("A:A"), xlSortOnCellColor, xlAscending, xlSortNormal) _ .SortOnValue.Color = rgbYellow .AutoFilter.Sort.Header = xlYes .AutoFilter.Sort.MatchCase = False .AutoFilter.Sort.Orientation = xlTopToBottom .AutoFilter.Sort.SortMethod = xlPinYin .AutoFilter.Sort.Apply .Columns("A:A").ColumnWidth = 50 'for aesthetics .Range("A1").SelectEnd With For Each cell In Range("A:A") If cell.Interior.Color = Excel.XlRgbColor.rgbYellow Then cell.Value = "http://www." & cell.Value ws.Hyperlinks.Add Anchor:=cell, Address:=cell.Formula End If Next cellfinishBox = MsgBox("Finished!" & vbNewLine & vbNewLine _ & "Duplicate URL's are at the top of the list.", _ vbOKOnly, "Finished") If finishBox = vbokay Then Exit Sub End IfEnd Sub
While automation is not advised for the majority of link building, certain tasks can be automated and every minute adds up. Integrating customer data, prospecting sites and outreach emails are great candidates for some sort of automation. Even a little automation can save you a ton of time and increase your business' profits. For example, let’s say that you are able to find duplicate sites without using this code in about 2 minutes and that you have ten clients that you check for unreported links every month. Using this code will save you about 20 minutes of work every month or 4 hours a year. Now let’s assume that an average employer cost for link building is $15 an hour. Using this code you could save your employer $60 a year. Think big and imagine that same employer has 50 link builders all performing the same task each month. This code will now save your employer $3,000 a year. There is always room for a little automation to save time and money, even in the world of high quality organic link building.