How do I delete all rows in column B that don't contain the same values as those in column A?
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.
you could set up a column C with something like:
then set up automatic filter, filter column C on value
set the formula:
filter, select and delete:
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:
In the column Check simply add this formula:
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:
In the Check column use an IF statement like
In both examples filter the tables on the value 0 in Check, select all rows and delete them.
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...
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...