匹配两个列，并获得匹配的列值以下(Matching two column and get value

我在A列一个透视表和列D.透视表一个正常的表将每月更新一次。我想，如果值不存在，然后在A列中的值必须进来列D.最后一个单元格下如果值匹配则没有复制和粘贴然后匹配列D.列A值。

我需要如图图像输出。我用下面的公式，但它不工作。它显示循环引用错误。我用它在D13。我怎样才能解决这个问题。帮我

这有可能在VBA

Excel公式我使用：

=IF(ISNUMBER(MATCH(A2;$D:$D;0));"";A2)

使用循环引用功能：

转到文件- >选项- >公式
在计算选项勾选启用迭代计算
设置最大的迭代1
输入下式在第一空白单元格Column D ：

=IF(COUNTIF($D$2:INDIRECT(ADDRESS(COUNTA(D:D),4)),A2)=0,A2,"")

它向下拖动，直到你把所有需要的值。
请注意，您仍然有空白单元格，这是不可能的功能范围之内删除。

使用VBA模块：

Option Explicit
Sub AddDict()
    Dim lRow As Long, iCell As Range
    Dim ClmnA As Range, ClmnD As Range
    Dim MySheet As Worksheet, iDict As Object
    '   Your worksheet, change "Test" accordingly
    Set MySheet = ThisWorkbook.Worksheets("Test")
    '   Create dictionary object
    Set iDict = CreateObject("Scripting.Dictionary")
    With MySheet
        '   Last row of the Column "A"
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        '   Range of Column "A" values starting from second row (without header)
        Set ClmnA = .Range("A2:A" & lRow)
        '   Last row of the Column "D"
        lRow = .Range("D" & .Rows.Count).End(xlUp).Row
        '   Range of Column "D" values starting from second row (without header)
        Set ClmnD = .Range("D2:D" & lRow)
    End With
    '   Loop through each cell in Column "D"
    For Each iCell In ClmnD.Cells
        '   Add cell value to dictionary (omitting duplicates)
        iDict(iCell.Value) = iCell.Value
    Next
    '   Loop through each cell in Column "A"
    For Each iCell In ClmnA.Cells
        '   Add cell value to dictionary (omitting duplicates)
        iDict(iCell.Value) = iCell.Value
    Next
    '   Populate Column "D" with dictionary items
    MySheet.Range("D2:D" & iDict.Count + 1) = Application.Transpose(iDict.Items)
End Sub