Excel - Compare Columns

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?

Thanks,
jimbo99999
Jimbo99999Asked:
Who is Participating?
 
k2exchCommented:
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.
0
 
k2exchCommented:
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.
0
 
Jimbo99999Author Commented:
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.

Thanks,
jimbo99999
0
 
NBVCCommented:
Assuming data in columns A and B, then in C2

=Isnumber(Match(B2,A:A,0))

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:

=ISNA(Match(B1,A:A,0))

Then click Format and choose from Pattern.  This will color cells that don't match column A.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.