Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How do I delete all rows in column B that don't contain the same values as those in column A?

share|improve this question

closed as unclear what you're asking by Canadian Luke, Tog, and31415, Doktoro Reichard, CharlieRB Jul 18 at 19:36

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

3  
Your question is a little confusing: do you want to remove every row where the value in B doesn't match the value in A, or do you want to remove every row where the value in B doesn't exist anywhere in A? –  CLockeWork Jun 19 '13 at 12:35
    
Welcome to superuser. Can you please edit your question to tell us what you've tried so far. Details are important, so also tell us if you are trying to just delete the values in column B or actually delete the entire row (which includes all columns). –  CharlieRB Jun 19 '13 at 12:35
    
Thanks for the help everyone. I used the checking method posted below and it suits my needs. Thanks for all of the replies. You guys are awesome :) –  Sam Jun 20 '13 at 16:41

3 Answers 3

you could set up a column C with something like: =IF(A1=B1;"OK";"KO")

then set up automatic filter, filter column C on value KO, and delete all the filtered lines...

example:

Step1

set the formula:

Step2

filter, select and delete:

Step2!

share|improve this answer
    
Although this is a good way to filter, the OP asked to "delete all rows in column B". –  CharlieRB Jun 19 '13 at 12:32
    
the question is unclear about that IMHO... –  pataluc Jun 19 '13 at 12:43
    
I think the OP wanted a ranged look up. To do what you did (a bit easier), you can just use the EXACT function. =EXACT(A1,B1) –  skub Jun 19 '13 at 14:08

In any case, without VBA, the best you can do is identify which rows to remove and then remove them manually.


If you want to remove rows where the value in B doesn't exist in A you can simply use a countif:

enter image description here

In the column Check simply add this formula: =COUNTIF($A$2:$A$8,B2) The fixed range is the range of your A column, and you'll need to fix the values so that it is always looking at the same range, of course with a table you can replace $A$2:$A$8 with [A]


If you want to find any row where the value in B doesn't match the adjacent value in A use the IF statement @pataluc gave you:

enter image description here

In the Check column use an IF statement like =IF($A2=$B2,1,0) and again if using a table instead do =IF([@[A]=[@[B],1,0)


In both examples filter the tables on the value 0 in Check, select all rows and delete them.

share|improve this answer

You want a formula like this and then you can sort the rows on "Is it in COL1?" and delete them. Sorry, I don't have an automated way to delete the rows without writing a macro for it. And I haven't gotten my coffee yet...

=IF(ISNA(VLOOKUP(A2,$B$2:$B$7,1,FALSE)),"NO","YES")

The IF checks if the value is in the lookup. The ISNA checks for "#N/A" (which means it wasn't found) and the NO/YES returns to the cell based on whether the IF is true (ISNA) or false (a found value).

Hope that makes sense.

--EDIT: the "$" (dollar signs) in the range make the range sticky. It keeps it the same value regardless if you drag it down to other cells or not. That's something you might want to know...

enter image description here

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.