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?
Right click to click Delete from the context menu to delete the rows which are not matching the criteria on another sheet.
To delete rows that contain these cells, right-click anywhere in the data range and from the drop-down menu, choose Delete.
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With