删除其他工作表的列中值为* not *的行 [英] Remove rows where value is *not* in column on other sheet

问题描述

我有一个包含两张纸的LibreOffice Calc文件.表格2仅有一列A，其中包含许多数字.在工作表1中，每行的A列也包含一个数字.我想从工作表1中删除所有在A列中具有值的行，这些行在工作表2的A列中 not 出现 anywhere .

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.

有什么想法吗?

推荐答案

在Sheet1的单元格B1中输入以下公式:

Enter the following formula in cell B1 of Sheet1:

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

然后将其拖动到B列下方.这将显示Sheet2中未出现的所有行的空白单元格.

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

要删除空行，请对B列进行排序(数据->排序).然后选择并删除空白行(编辑->删除行).

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

这篇关于删除其他工作表的列中值为* not *的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！