Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Excel - Compare Columns

Posted on 2012-09-19
Medium Priority
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
  • 2

Assisted Solution

k2exch earned 1800 total points
ID: 38415436
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

ID: 38415464
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

NBVC earned 200 total points
ID: 38415478
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.

Accepted Solution

k2exch earned 1800 total points
ID: 38416598
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
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…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

571 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