Excel - Compare Columns

Posted on 2012-09-19
Last Modified: 2012-09-24
Good Day Experts!

I am in a bit of a quandry here.  I have 2 columns of data in my spreadsheet.  One column has 46419 count and the other one has 49004 count.  

I am interested in finding the ones that are in the second column that are not in the first column.

How can I find the differences?

Question by:Jimbo99999
    LVL 1

    Assisted Solution

    use Vlookup function. for example in third column first cell write formula Vlookup(B2,A:A,1,FALES). then drag the cell to words the end. then filter the column and find filter out cells with N/A. Corresponding cell data in B column is not in column A.

    Author Comment

    Ok, I think that will work for me...thanks.  But how can I copy formula to all the cells? My column is almost 50,000 long.

    LVL 23

    Assisted Solution

    Assuming data in columns A and B, then in C2


    this will give TRUE if match is found and FALSE if it is not found in column A

    copy down formula.

    You can also use Conditional Formatting after selecting column B and going to Home|Conditonal Format|New Rule, select to use a formula to determine which cells to format, then  enter formula:


    Then click Format and choose from Pattern.  This will color cells that don't match column A.
    LVL 1

    Accepted Solution

    Hi Jumbo,

    Its quite easy in excel to copy formula to full row. Just enter formula in first cell. When you click on any cell in excel cell's border is set to dark black and on buttom right side of cell you will see a bold Dot (.) then move you mouse to that Dot (.). it will change your mouse cursor to + sigh.. so just hit double click there. your formula will be pasted automatically till end for your nearest column with all changes inside formula related to cell numbers references . so its quite easy in excel to use formulas.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now