ExcelBanter - Finding difference between two columns

"Jim Bancroft" wrote in message ...
Hi all,

I have two columns of numbers which *should* match but don't. One
column stretches out longer than the other, and I'd like to create in a
third column that has all entries in Column A not in Column B. Can anyone
reccomend a way to do this? I've sorted both columns in ascending order, if
that helps.

Thanks,

-Jim

OK,

I am a rookie but I just had to do this. There are always 10 ways to
do anything. With some help I ended up with some complicated VBA code
that I do not really understand.

Below is the quick and dirty way to do the job, maybe not the most
elegant. But it worked for me!!

I would open a new blank workbook and do this (you do not need to
worry about sorting and I am assuming no header row):

1) Copy the long column to Sheet1 Column B

2) Copy the short column to Sheet2 Column A (make a note of the last
row, you will not need to go back to Sheet2 after this)

3) Copy this formula into cell A1 on Sheet1

4) =COUNTIF(Sheet2!A$1:A$9,B1)

5) Adjust the range: A$1:A$9 to match the last row in Sheet2

6) Drag the formula in Sheet1 cell A1 to the last row in Sheet1 column
B

7) You should now have a series of 1's and 0's. The 0's are the
entries that are
in the long column (Sheet1) that are NOT in the short column (Sheet2).

8) Now turn on AutoFilter: Data, Filter, Autofilter

9) Filter Sheet1 column A to show only 0's. This is your new list you
can copy to a new column.

(You can do this without copying the columns to seperate sheets, but
it is less confusing this way. Also if you have other columns with
data you will want to copy all of them to the new workbook.)

Fred