Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding difference between two columns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the difference | Excel Discussion (Misc queries) | |||
finding date difference | Excel Discussion (Misc queries) | |||
Finding the difference | Excel Discussion (Misc queries) | |||
charting a difference of 2 columns' w/o adding a difference column | Charts and Charting in Excel | |||
Finding Time difference | Excel Worksheet Functions |