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
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
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.
- Ranking of Top Data Scientists on Twitter using MajesticSEO’s Metrics - August 19, 2014
- Measuring Twitter Profile Quality - August 14, 2014
- PageRank, TrustFlow and the Search Universe - July 7, 2014
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 pmYou 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 pmHi 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> Thank you. I will try to incorporate this in a future template.
October 14, 2013 at 10:00 amThese 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> I had thought of that, but then google.co.uk and barclays.co.uk both reduce to co.uk
October 14, 2013 at 9:59 amSEOTools for Excel will do this for you! Check =URLPROPERTY(Cell;”host”;)
http://nielsbosma.se/projects/seotools/functions/urlproperty/
October 14, 2013 at 1:56 pmThanks 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 pmThank you for this news!
October 13, 2013 at 1:26 amI also want to say bravo, Majesticseo is an excellent tool for analiser links and improve its SEO.
Nice job!
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> Hi Nick:
First off,:
November 1, 2013 at 6:20 pm1) 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.
This is great, it will make it easier when searching for new domains, keywords! Thanks a bunch!
November 1, 2013 at 2:06 pmJoyce