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.

Neep Hazarika

Comments

  • Dariusz Romanowski

    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?

    October 9, 2013 at 2:30 pm
  • Neep Hazarika

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

    October 9, 2013 at 3:47 pm
  • Dimitri Prosvirin

    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)

    October 10, 2013 at 8:27 am
  • Jason McCuen

    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!

    October 10, 2013 at 3:45 pm
  • Brent Rangen

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

    October 11, 2013 at 7:44 pm
  • Luc bizet

    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!

    October 13, 2013 at 1:26 am
  • Nick Davidson

    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

    November 1, 2013 at 3:41 am
    • Neep Hazarika

      > Hi Nick:

      First off,:
      1) 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.

      November 1, 2013 at 6:20 pm
  • Joyce

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

    November 1, 2013 at 2:06 pm

Comments are closed.