7

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?

Community
  • 1
  • 1
Lukas Barth
  • 2,734
  • 18
  • 43

1 Answers1

11

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).

Jim K
  • 12,824
  • 2
  • 22
  • 51
  • 1
    Works great, but I had to substitute the "," with ";" for separating the arguments. Thanks! – Lukas Barth Jan 03 '16 at 02:35
  • Commas separating arguments works as of now. – Darrell Ulm Jul 20 '21 at 20:11
  • 1
    @DarrelUlm: Commas only work if they are set as the function separator in *Tools > Options > LibreOffice Calc > Formulas*. Semicolons work no matter what the setting, although as far as compatibility, they do not work in MS Excel. – Jim K Jul 21 '21 at 13:28