excel delete row if column contains value from to-remove-list

问题描述

假设我有一张表格第一个 - 超过5000行（比如，列' A ' - '

在另一张表格 - 第二个 - 我有一个删除列表 - 一个单一的列 A ，其中包含400个值，每个值都包含字母数字字符串（例如：xxx1234）。

每个整行都必须删除从表格第一个，如果列 E 包含to-remove-list中的任何值（从列的 A

通过删除整行，我的意思是删除该行并将其移动（不留空格）

如何实现？任何帮助将不胜感激。

解决方案

给定表2：

  ColumnA 
 ------- 
 apple 
 orange 
  

您可以在表格1中标记表2中存在值的行：

  ColumnA ColumnB 
 ------- -------------- 
 pear = IF（ISERROR（VLOOKUP（A1，Sheet2！A：A，1， 
 apple = IF（ISERROR（VLOOKUP（A2，Sheet2！A：A，1，FALSE）），保留，删除）
 cherry = IF（ISERROR（VLOOKUP（A3，Sheet2！A：A，1，FALSE）），Keep，Delete）
 orange = IF（ISERROR（VLOOKUP（A4，Sheet2！A：A， 1，FALSE）），Keep，Delete）
 plum = IF（ISERROR（VLOOKUP（A5，Sheet2！A：A，1，FALSE）），Keep，Delete $ b  

生成的数据如下所示：

  ColumnA ColumnB 
 ------- -------------- 
 pear保持
苹果删除
樱桃保持
橙色删除
李子保持
  

Yo您可以轻松过滤或排序表格1，并删除用删除标记的行。

1. Let's say that I've got a sheet - number one - with over 5000 rows (say, columns 'A' - 'H' each).
2. In another sheet - number two - I have a "to-remove-list" - a single column 'A' with 400 values, each containing alphanumerical string (example: xxx1234).
3. I have to remove every entire row from sheet number one, if column 'E' contains any value from "to-remove-list" (from column 'A' of sheet number two).
4. By removing the entire row, I mean delete the row and move it up (not leaving the blankspace)

How do I achieve that? Any help would be much appreciated.

解决方案

Given sheet 2:

ColumnA
-------
apple
orange

You can flag the rows in sheet 1 where a value exists in sheet 2:

ColumnA  ColumnB
-------  --------------
pear     =IF(ISERROR(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),"Keep","Delete")
apple    =IF(ISERROR(VLOOKUP(A2,Sheet2!A:A,1,FALSE)),"Keep","Delete")
cherry   =IF(ISERROR(VLOOKUP(A3,Sheet2!A:A,1,FALSE)),"Keep","Delete")
orange   =IF(ISERROR(VLOOKUP(A4,Sheet2!A:A,1,FALSE)),"Keep","Delete")
plum     =IF(ISERROR(VLOOKUP(A5,Sheet2!A:A,1,FALSE)),"Keep","Delete")

The resulting data looks like this:

ColumnA  ColumnB
-------  --------------
pear     Keep
apple    Delete
cherry   Keep
orange   Delete
plum     Keep

You can then easily filter or sort sheet 1 and delete the rows flagged with 'Delete'.

这篇关于excel delete row if column contains value from to-remove-list的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！