LibreOffice / OpenOffice Calc: Remove rows where v

Question

I have a LibreOffice calc file with two sheets. Sheet 2 has just one column A with lots of numbers. In sheet 1, column A of every row also holds a number. I want to remove all rows from sheet 1 that have a value in column A which does not appear anywhere in column A of sheet 2.

Filters don't seem to do the trick, as they don't have a "value must be contained in some column" operator.

Any ideas?

Answer 1

Enter the following formula in cell B1 of Sheet1:

=IF(ISNA(VLOOKUP(A1,Sheet2.A:A,1, 0)),"",A1)

Then drag to fill this formula down column B. This shows empty cells for all rows that do not occur in Sheet2.

To remove the empty rows, sort on column B (Data -> Sort). Then select and delete the empty rows (Edit -> Delete Rows).