英文:
Excel: How to identify duplicate values in separate Excel files
问题
我有两个单独的Excel文件，它们都有一个名为"Q"的列。
如果这两个文件在"Q"列中的任何位置都有相同的值，我需要确定文件1中具有该值的行。
目标：
- 我需要确保文件1的"Q"列只包含文件2的"Q"列中不存在的值。
- 如果文件1包含一个在文件2的"Q"列中也存在的"Q"列值，我希望在文件1中突出显示或删除该行。
我尝试过的方法：
- 我将这两个文件添加到同一个工作簿中，以解决引用问题。现在文件1为"Sheet1"，文件2为"Sheet2"。
我在文件1的"Q"列的每个单元格中应用了以下公式：=COUNTIF(Sheet2!$Q$2:$Q$3413, Q1)>0
3413是文件2中的行数。
这种方法会突出显示每一行。
我的主要想法是我可能没有引用正确的文件，但是条件格式执行而不提供错误消息的事实使我相信这可能不是问题所在。
更新：
我现在尝试将文件2的"Q"列（1220行）复制到文件1的"R"列（3413行）中。
然后，我在文件1的"Q"列的每个单元格中应用了以下公式：=ISNUMBER(MATCH(Q2,$R$2:$R$3413,0))
我仍然遇到相同的问题，每一行都被突出显示。
英文:
I have two separate Excel files that have a column "Q".
If both files have the same value anywhere in column "Q", I need to identify the row that has this value in file #1.
OBJECTIVE:
-
I need to ensure that file #1'S column "Q" only consists of values that are not present in file #2's column "Q".
-
If file #1 contains a row that has a column "Q" value in column "Q" of file #2, I want to highlight or remove this row in file #1.
WHAT I HAVE TRIED:
- I added both files in the same workbook to solve reference issues. So now file #1 = "Sheet1" and file #2 = "Sheet2".
I applied the following formula to every cell within file #1's column "Q" --> =COUNTIF(Sheet2!$Q$2:$Q$3413, Q1)>0
3413 is the amount of rows in file #2.
This approach highlights every single row.
My main thought is that I'm simply not referencing the correct files, but the fact that the conditional formatting executes rather than providing an error message leads me to believe that this may not be the case.
UPDATE:
I have now tried copying file #2's column Q ( 1220 rows ) into file #1's column R ( 3413 rows ).
-
I then applied the following formula to every cell in file #1's column Q: =ISNUMBER(MATCH(Q2,$R$2:$R$3413,0))
-
I am still experiencing the same behavior, every row is being highlighted.
答案1
得分: 1
这将起作用（为了清晰起见，两个列表都在同一张表上）
英文:
This will work (both lists on same sheet for clarity)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论