英文:
Excel: Finding items in a column in another column and deleting them
问题
我正在尝试使用列在右侧（列H）的以下名称（Nancy、Daniel、Sergio、Patty）在左侧的表格中查找它们并删除它们（以及它们所在的整行）。
以下是已删除行的最终表格示例：
英文:
I'm trying to use the following names (Nancy, Daniel, Sergio, Patty) that are listed on the right-side column (column H) finding them on Table that is on the left-side and deleting them (along its whole row).
Here's how the final table would look like with the deleted rows:
Could this be done with a simple VLOOKUP formula?
答案1
得分: 1
如果你愿意只隐藏包含列表中姓名的行：
- 将当前数据格式化为表格（应该是CTRL+T）。
- 在表格中添加列‘Hide’。
- 在‘Hide’列中添加此公式：
COUNTIF(H2:H5, A2) - 确保公式正确定义整个列。
- 此列应该只包含‘1’和‘0’。将其筛选以仅显示‘0’，完成。
- 或者你可以筛选以仅显示‘1’，如果愿意，可以删除它们。
英文:
If you are okay with just hiding the rows which contains a name in the list:
- Format the current data as table (should be <kbd>CTRL</kbd>+<kbd>T</kbd>).
- Add column 'Hide' to the table.
- Add this formula in the 'Hide' column:
COUNTIF(H2:H5, A2) - Make sure the formula is correctly defined for the entire column.
- You should have only '1's and '0's in this column. Filter it to show only '0' and you are done.
- Or you can filter to show only the '1's and delete them if you wish that.
答案2
得分: 1
你不能使用公式删除行。要实现这一功能，你需要使用 VBA 代码。但是，使用 FILTER 公式，你可以创建一个新表，排除在 H 中的列表：
`=FILTER(A1:C7,ISNA(MATCH(A1:A7,H2:H4,0)))
结果：
英文:
You can't delete rows using formula. For doing that you would need VBA code. But with FILTER formula you could crate new table excluding list in H:
=FILTER(A1:C7,ISNA(MATCH(A1:A7,H2:H4,0)))
Result:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论