vba Excel - 需要查找是否在 B 列中找到 A 列中的任何内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17281593/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel - Need to find if anything from column A is found within column B
提问by Solomon Closson
Basicaly, I have 2 lists of email addresses in Excell...
基本上，我在 Excell 中有 2 个电子邮件地址列表...
Column A = Holds over 2051 rows of email addresses
Column B = Holds about 1994 rows of email addresses
I need a way to find out which email addresses in Column A aren't found in Column B, and preferably output the results in, either a new sheet, or in Column C.
我需要一种方法来找出 A 列中的哪些电子邮件地址在 B 列中找不到，并且最好将结果输出到新工作表或 C 列中。
Than after that I need to be able to find which email addresses in Column B aren't found in Column A (if any) and output that list into, either a new sheet or Column D.
在那之后，我需要能够找到在 A 列（如果有）中找不到 B 列中的哪些电子邮件地址，并将该列表输出到新工作表或 D 列中。
How can I do this?
我怎样才能做到这一点？
回答by ApplePie
In either a new sheet or column C use a combination of VLOOKUP() and IFERROR() and drag that formula for every line of A.
在新工作表或 C 列中使用 VLOOKUP() 和 IFERROR() 的组合，并为 A 的每一行拖动该公式。
=IF(ISERROR(VLOOKUP(A1, $B$1:$B$1995, 1, 0)), A1 & " NOT FOUND IN COLUMN B", "FOUND IN B")
=IF(ISERROR(VLOOKUP(A1, $B$1:$B$1995, 1, 0)), A1 & " NOT FOUND IN COLUMN B", "FOUND IN B")
This will return two different messages depending on if the e-mail was found or not in B.
这将返回两个不同的消息，具体取决于是否在 B 中找到了电子邮件。
回答by Aaron Cesari
Why not copy paste the data from column B onto the end of column A? Then set the conditional formatting for the column to highlight all items whose count exceeds one. Use this formula, "=countif($A$1:A1,A1)>1", without the quotes. Make sure the whole column is selected when doing this.
为什么不将 B 列中的数据复制粘贴到 A 列的末尾？然后为该列设置条件格式以突出显示计数超过 1 的所有项目。使用这个公式“ =countif($A$1:A1,A1)>1”，不带引号。确保在执行此操作时选择了整列。
Another method for maintaining the separation of data. In column C use a formula like this =IF(ISERROR(VLOOKUP(A1,$B$1:$B$100,1,0)),A1,""); change the ranges to match your data ranges. Then fill down the formula until the end of data in column A. To fill down, select the desired range and press 'Cntl+D'. Repeat this for column D but swap the A and B references in the formula and fill down until the bottom of the column B data. This will result in data in columns C & D that list the unique values. Copy and paste these values, be sure to paste as values if the default paste is used Excel will paste the formulas and not the data, into another set of columns (E & F) or the same columns, then sort each column to eliminate the spaces.
另一种保持数据分离的方法。在 C 列中使用这样的公式=IF(ISERROR(VLOOKUP(A1,$B$1:$B$100,1,0)),A1,"")；更改范围以匹配您的数据范围。然后向下填充公式，直到 A 列中的数据结束。要向下填充，请选择所需的范围并按“Cntl+D”。对 D 列重复此操作，但交换公式中的 A 和 B 引用并向下填充，直到 B 列数据的底部。这将导致列 C 和 D 中的数据列出唯一值。复制并粘贴这些值，如果使用默认粘贴，请确保粘贴为值 Excel 会将公式而非数据粘贴到另一组列 (E & F) 或相同列中，然后对每列进行排序以消除空间。
回答by matzone
You can use this Sub ..
您可以使用此 Sub ..
Sub CrossCheck()
Dim LastA, LastB, r As Range
Dim x, Cn, Dn As Long
Set LastA = Range("A65536").End(xlUp)
Set LastB = Range("B65536").End(xlUp)
Cn = 1
Dn = 1
For x = 1 To LastB.Row
Set r = Columns("B").Find(Cells(x, 1), , xlValues, xlWhole)
If r Is Nothing Then
Cells(Cn, 3) = Cells(x, 1)
Cn = Cn + 1
End If
Next
For x = 1 To LastA.Row
Set r = Columns("A").Find(Cells(x, 2), , xlValues, xlWhole)
If r Is Nothing Then
Cells(Dn, 4) = Cells(x, 2)
Dn = Dn + 1
End If
Next
End Sub
Hope this help !!
希望这有帮助！！
