Excel - 如何删除1张表中不包含来自另一张表的列输入的所有行

I have a list of email addresses in 1 sheet in my Excel Book that are on Column M (Sheet1) with 2050 rows long (there is other data on other columns, such as First and Last Name, etc.), and another Sheet (Sheet2) that have a list of email addresses in Column A (210 rows long). I need to keep all email addresses in Sheet1 - Column M (need to keep entire row) that match with the email addresses in Sheet2 - Column A, but need to remove all other rows where email addresses that do not match from Sheet2 column A with Sheet1 - Column M.

我的Excel工作簿中有一个电子邮件地址列表，列在M列（Sheet1）上，长度为2050行（其他列上有其他数据，如名字和姓氏等），另有一张表（ Sheet2）在A列中有一个电子邮件地址列表（210行长）。我需要保留Sheet1中的所有电子邮件地址 - 列M（需要保留整行）与Sheet2 - A列中的电子邮件地址匹配，但需要删除与Sheet2列A中的电子邮件地址不匹配的所有其他行Sheet1 - M栏。

Is there a formula to do this?

有这样的公式吗？

Or Perhaps it can create another sheet (Sheet3) with the rows from Sheet1 that match Sheet1 - Column M with Sheet2 - Column A?

或者也许它可以创建另一张表（Sheet3），其中Sheet1中的行与Sheet1匹配 - 列M与Sheet2 - 列A？

Thanks very much.

非常感谢。

1 个解决方案

#1

Here's one method but doesn't use just a formula:

这是一种方法，但不仅使用公式：

Setup an additional column as in "N" below. and fill using a similar formula

设置一个附加列，如下面的“N”所示。并使用类似的公式填写

• Turn on filtering for your sheets and filter for "no"
  • data --> filtering click on drop down for "N" and un-select yes/blanks
  • data - > filtering点击下拉列表中的“N”并取消选择yes / blanks
• 打开过滤器并过滤“否”数据 - >过滤单击下拉列表中的“N”并取消选择是/空白
• now place cursor in cell aX (where x will be the first row that is no)
• 现在将光标放在单元格aX中（其中x将是第一行为no）
• press ctrl-end to highlight all rows that are "No"
• 按ctrl-end突出显示“No”的所有行
• Now press delete.
• 现在按删除。

Or you can copy and paste the "YES" using filtering to another sheet.

或者您可以使用过滤复制并粘贴“是”到另一张纸。

I believe the crux of the issue here was identifying the records that didn't match which the formula in the image above: and here: =IF(ISNUMBER(MATCH(M5,Sheet2!A:A,0)),"Yes","No") does

我认为这里问题的症结在于确定与上图中的公式不匹配的记录：这里：= IF（ISNUMBER（MATL（M5，Sheet2！A：A，0）），“是” ，“不”）