Some Useful Excel Templates

By Neep Hazarika October 9, 2013

Compare two Lists

Compare two lists.

Compare two lists.

Sometimes, it may be necessary to compare data between two columns  in an Excel spreadsheet. It is particularly important, when analysing backlinks for example, to de-duplicate lists of data into some kind of useful list of domains that is easier to analyse. For instance, if you  have two columns of different lengths filled with URL names, you may want to compare these two lists to select all URLs that are common to both lists A and B, values that are in column A but not in column B, and values in column B but not in column A. Knowing how to compare data in Excel is useful for record keeping and list management. For example, comparisons can be used to make sure the same data is not entered twice, or to check that the correct information has been entered for a record. To achieve this, it is possible to employ a variety of methods: use a formula, or write a Visual Basic Macro. Here, I will provide a simple template that uses the former method. The template can be downloaded here. Columns A and B in the spreadsheet correspond to the two lists that have to be compared. Column C displays all values that are common to both columns A and B, column D shows values that exist in column A only, and column E shows values that exist in column B only.

Extracting Domain Names from URLs

Compare two lists in Excel.

Extract Domains from URLs

Search Engine Optimization (SEO) frequently entails dealing with long lists of URLs. One of the best  ways to manage this is with Excel. Sometimes it may be necessary to parse a list of URLs in order to obtain the domain names.  This can be quite time-consuming if attempted manually, but with a few Excel formulas it is possible to extract the domain name from a long URL. For example, if you have a list of URLs like this:

  • https://www.majesticseo.com/plans-pricing
  • http://www.google.co.uk/adplanner/static/top1000/
  • http://stackoverflow.com/tags/mallet/hot

it is possible to reduce this list and so that it contains only the domain portions of the URLs as shown below:

  • www.majesticseo.com
  • www.google.co.uk
  • stackoverflow.com

An example of how this method can be used is with competitor backlink analysis to quickly identify all of the unique domains that  link to  particular websites. There are many other potential applications for domain name extraction in areas such as search engine optimization (SEO), publicity, online marketing, analytics and web development, where reporting and comparing data is essential. An Excel template that can perform this task can be downloaded here.

Create a list of 100 random domain names

Generate a list of domains from random words.

Generate a list of domains from random words.

Sometimes in SEO analysis, it helps to create a list of domains with no obvious correlation to the research you are working on. Using domain lists from the Internet tend to create lists that have a bias in relation to the research. This template is one that uses 100 randomly generated words and then connects random TLD suffixes to them to create a list of domain names which are likely to exist, but with no preconceived notion of quality or context.

Posted In: Research

12 Responses to “Some Useful Excel Templates”

  1. Dariusz Romanowski said:

    October 09, 2013 at 2:30 pm

    Thnx neep, you first excel template is especially for me:) I own you a beer:) Im thinking about visualization for the results, what do you think about that?

  2. Neep Hazarika said:

    October 09, 2013 at 3:47 pm

    You could create bar or column charts in Excel to display the proportions of the number of items present in each list.

  3. Dimitri Prosvirin said:

    October 10, 2013 at 8:27 am

    Hi guys,

    good stuff. One small hint you might find useful for Extracting Domain Names file – sometimes it is needed to also remove “www” or anything before the actual domain name (for example to compare with domain list from Google webmaster tools).

    =LEFT(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(SEARCH(“//”,A3)),MID(A3,SEARCH(“//”,A3)+2,255),A3),”www.”,”"),”ww.”,”"),FIND(“/”,SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(SEARCH(“//”,A3)),MID(A3,SEARCH(“//”,A3)+2,255),A3),”www.”,”"),”ww.”,”"))-1)

    • Neep Hazarika said:

      October 14, 2013 at 10:00 am

      > Thank you. I will try to incorporate this in a future template.

  4. Jason McCuen said:

    October 10, 2013 at 3:45 pm

    These are really helpful templates! One thing that would be nice for the Domain Extraction Template would be to remove everything but the root domain.

    Thanks for the great share!

  5. Brent Rangen said:

    October 11, 2013 at 7:44 pm

    Thanks for sharing. List comparison is cool, I just modified by adding in a total count for common occurrences. Very cool.

  6. Luc bizet said:

    October 13, 2013 at 1:26 am

    Thank you for this news!
    I also want to say bravo, Majesticseo is an excellent tool for analiser links and improve its SEO.
    Nice job!

  7. Nick Davidson said:

    November 01, 2013 at 3:41 am

    Hi,

    Many thanks for these! Can I just ask a quick question in relation to using the Compare Two Lists…

    I have tried using a list of 79 domain names in column A, and a list of 38 domain names in column B.

    I am getting 12 as being Common but only 21 in each of A Only and B Only.

    I know all the domain names are unique so the maths doesnt seem to work for the A or B only columns.

    Can you help me? Am I missing something obvious.

    Regards,

    Nick

    • Neep Hazarika said:

      November 01, 2013 at 6:20 pm

      > Hi Nick:

      First off,:
      1) http://www.xxx.com and xxx.com are not unique for this particular application.
      2) Secondly, in Excel, select all columns with data on column 2 and drag everything down till row 79. Then do the same for columns 3 and 4. Let me know if this helps.

  8. Joyce said:

    November 01, 2013 at 2:06 pm

    This is great, it will make it easier when searching for new domains, keywords! Thanks a bunch!
    Joyce