Excel-如何删除1个工作表中不包含来自另一工作表的列输入的所有行 [英] Excel - How to Delete All rows from 1 sheet that do not contain column input from another sheet

问题描述

我在Excel簿的1张工作表中有一个电子邮件地址列表，该列表位于M列(Sheet1)上，该列的长度为2050行(其他列上还有其他数据，例如名和姓等)，并且另一个工作表(Sheet2)，该工作表在A列(长210行)中有一个电子邮件地址列表.我需要将工作表Sheet1-M列中的所有电子邮件地址都保留(需要保留整行)，以与工作表Sheet2-A列中的电子邮件地址相匹配，但是需要删除所有其他与Sheet2列A中的电子邮件地址不匹配的行Sheet1-M列.

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.

有公式可以做到这一点吗?

Is there a formula to do this?

或者也许它可以使用Sheet1中与Sheet1-M列匹配Sheet2-A列匹配的行来创建另一个工作表(Sheet3)?

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

非常感谢.

推荐答案

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

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

设置一个附加列，如下面的"N"所示.并使用类似的公式填充

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

    或者您可以使用过滤将是"复制并粘贴到另一张纸上.

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

    我认为问题的症结在于确定与上图中的公式不匹配的记录:此处:=IF(ISNUMBER(MATCH(M5,Sheet2!A:A,0)),"Yes","No")可以做到

    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

    这篇关于Excel-如何删除1个工作表中不包含来自另一工作表的列输入的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！