Compare columns & remove matches

**x1372** · 08-13-2009, 05:40 PM

Hello,

I've been searching for a method to compare two large columns of numbers and remove replicates from the "main" column. Column A has all the numbers that are in column B and many more. I want to remove all the numbers from column A that are in column B. The result being two columns with no matching numbers. (or a third column that has only the numbers from 'A' that are not in 'B').

My search results on this forum have found results that compare rows for matches & can make deletions; and also one method that could mark all the entries that were duplicates in both columns, but nothing to remove them. The datasets I am using have over 400k entries in some columns, so manual removal of marked ones is not feasible. I need an automatic deletion method.

Thank you in advance for any assistance you can provide.

-AJP

**martindwilson** · 08-13-2009, 05:51 PM

use advanced filter(safest is to copy column a and b to a new sheet and work on that)
rename column b header to same as column A
now select column a choose advanced filter the data in column a will be in the top field
in criteria select column b including header click filter in place click ok
now all the rows in col a that match b wil be shown
select all those rows by the row numbers
right click delete rows
turn of filter.
oh you have 2007 see images

**x1372** · 08-14-2009, 09:35 AM

Martin,

Yes, that does solve the problem in a quick and straightforward way. I just need to remember to go back to the original page to use the "B" column since the filtering method removes some of the data from it on the copied page.

However, I need to reduce the length of my data columns. Using this method with 400k+ values went on for over 20 minutes before I cancelled the operation. I can split my columns into shorter pieces and use this on a sample in order to get the results I need.

Thank you very much for your help!

-AJP