比较两个数据集的匹配和不匹配

Question

我在两个不同的表中有数据。Sheet1。A将包含一个字母数字条目"ABC123"和Sheet2。A将包含类似的条目"ABC123某些文本"或"某些文本ABC123"

另外，Sheet1的条目总是比Sheet2少，因此会出现不匹配。

在Sheet3中，我希望能够显示Sheet1的所有条目。A与其对应的Sheet2中的匹配项。A，然后对于所有不匹配，我希望它们显示在列表的底部。

理想输出示例:

Sheet3.A  Sheet3.B
ABC123    ABC123
ABC222    ABC222
ABC333    ABC333
          ABC444
          ABC555
          ABC666

目前我正在使用一个索引匹配(与LEFT函数)公式为Sheet3。B，但不能产生理想的输出:

Sheet3.A  Sheet3.B
ABC123    ABC123
ABC222    ABC222
ABC333    ABC333
          ABC444
          ABC444
          ABC444

也因为我使用LEFT函数和Sheet2中的数据。A的排列不能与Sheet1相似。A，有些条目找不到，因此产生#N/A

我还想添加Sheet2。A可能包含超过256个字符，这会导致索引匹配函数出现问题。这个问题不是当务之急，但如果能解决，那就太好了。

编辑:

问题和接受的答案现在正确地反映在一起

Answer 1

您可以使用.Find方法，搜索部分匹配。

Sub FindPartialString()
Dim wsList As Worksheet
Dim wsSearch As Worksheet
Dim wsOutput As Worksheet
Dim lastRow As Long
Dim rngList As Range
Dim rngMatch As Range
Dim cl As Range
Dim arrNonMatches() As Variant
Dim nonMatchCount As Long

Set wsList = Sheets(1) '## Modify as needed
Set wsSearch = Sheets(2) '## Modify as needed
Set wsOutput = Sheets(3) '## Modify as needed
Set rngList = wsList.Range("A2:A5") '## Modify as needed
For Each cl In rngList
    Set rngMatch = Nothing 'clear the container before each query
    'look for a partial match:
    Set rngMatch = wsSearch.Cells.Find(What:=cl.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    'Store the matches and non matches in separate arrays:
    If Not rngMatch Is Nothing Then
        lastRow = 1 + Application.WorksheetFunction.CountA(wsOutput.Range("A:A"))
        'put the searched value in column A:
        wsOutput.Cells(lastRow, 1) = cl.Value
        'Put the found value in column B:
        wsOutput.Cells(lastRow, 2) = rngMatch.Value
    Else:
        'store non-matches in an array
        ReDim Preserve arrNonMatches(nonMatchCount)
        arrNonMatches(nonMatchCount) = cl.Value
        nonMatchCount = nonMatchCount + 1
    End If
Next
'Print out the non-matches
lastRow = lastRow + 1
wsOutput.Cells(lastRow, 1).Resize(UBound(arrNonMatches) + 1, 1).Value = Application.Transpose(arrNonMatches)
End Sub