Comparing and deleting

Posted by Jon on August 02, 2001 10:40 AM

Anyone know how to do this? Column A contains 5,000 names, Column B
contains 1,000 of the same names. How do I match column A & B names so that
I can keep the 4,000 that do not have matches.

Posted by Cory on August 02, 2001 11:41 AM

I'm not sure what you mean about "keep the 4000 names", but to get the thousand that match try this:
In cell C1 enter this formula:
=IF(ISNA(VLOOKUP(A1,$B$1:$B$100,1,FALSE)),"",VLOOKUP(A1,$B$1:$B$100,1,FALSE))

then go to cell a1 and do the following:

hit End, then DownArrow
RightArrow 2 times (now in col C at the bottom of the list of names in col A)
Hold Shift then hit End, then UpArrow
now hit Alt + E, then I, then D.

now just hide column B and you should have a column of names next to Col A where the names match, and nothing showing next to names where there is no match...

That what you wree looking for?

Cory

Posted by Cory on August 02, 2001 11:59 AM

Wait!!

Wait! I just figured out what you really want after reading your post more closely. I'll have to work on it a minute (unless someone else answers first)...

Cory

Posted by Robb on August 03, 2001 5:14 AM

Cory is correct, but seems to be keeping the matches rather than the non matches. Assuming the 1000 names in B all appear in A, I think the formula would work as:
=IF(ISNA(VLOOKUP(A1,$B$1:$B$1000,1,FALSE)),A1,"")