我想比较Excel中不同工作表中的两个列表以查找任何重复项 [英] I want to compare two lists in different worksheets in Excel to locate any duplicates

问题描述

我知道这很简单，但是我仍然需要帮助:

I know this is very simple but I still need help:

我列出了已完成培训的属性.我需要尚未接受此培训的人员的姓名，但是系统不会给我这样的列表.但是，我列出了我们所有的属性.这意味着我必须比较不同工作表中的两列.

I have a list of properties that have finished a training. I need the names of the ones that have not done this training, but the system does not give me such a list. However, I have a list of all our properties. That means I have to compare two columns in different worksheets.

属性由 ID 数字标识.让我的生活更轻松的是，如果有一个 formula 可以检测到 duplicates 并在完整的酒店列表中突出显示，那么未突出显示的酒店就不会训练.

The properties are identified by ID Numbers. What would make my life easier is that if there is a formula that can detect duplicates and highlight on the complete list of hotels so the ones that are not highlighted have not done the training.

让我知道您是否对我有任何建议！

Let me know if you have any recommendations for me!

推荐答案

没有VBA ...

如果可以使用帮助器列，则可以使用 MATCH 函数测试一列中的值是否存在于另一列(或另一工作表的另一列)中.如果没有匹配项，它将返回错误

If you can use a helper column, you can use the MATCH function to test if a value in one column exists in another column (or in another column on another worksheet). It will return an Error if there is no match

要简单地识别重复项，请使用帮助器列

假设在Sheet1的A列中有数据，在Sheet2的A列中有另一个列表.在助手列的第1行中，放置以下公式:

Assume data in Sheet1, Column A, and another list in Sheet2, Column A. In your helper column, row 1, place the following formula:

= If(IsError(Match(A1，'Sheet2'！A:A，False))，"，重复")

向下拖动/复制此论坛，它应该识别出重复项.

Drag/copy this forumla down, and it should identify the duplicates.

要突出显示单元格，请使用条件格式:

进行一些修补后，您可以在条件格式设置规则中使用此 MATCH 函数，该规则将突出显示重复的值.我可能会使用辅助列来代替 ，尽管在​​创建条件格式设置规则之前，辅助列是一种查看"结果的好方法.

With some tinkering, you can use this MATCH function in a Conditional Formatting rule which would highlight duplicate values. I would probably do this instead of using a helper column, although the helper column is a great way to "see" results before you make the conditional formatting rule.

类似的东西:

= NOT(ISERROR(MATCH(A1，'Sheet2'！A:A，FALSE)))

对于Excel 2007和更低版本，您不能使用引用其他工作表的条件格式设置规则.在这种情况下，请使用helper列，并在A列中设置格式设置规则，例如:

For Excel 2007 and prior, you cannot use conditional formatting rules that reference other worksheets. In this case, use the helper column and set your formatting rule in column A like:

= B1 =重复"

此屏幕截图来自2010 UI，但相同的规则在2007/2003 Excel中也应适用.

This screenshot is from the 2010 UI, but the same rule should work in 2007/2003 Excel.

这篇关于我想比较Excel中不同工作表中的两个列表以查找任何重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！