匹配两列并获取匹配列以下的值 [英] Matching two column and get values below the matched column

问题描述

我在A列中有一个数据透视表，在D列中有一个普通表.数据透视表每个月都会更新.我想将A列的值与D列进行匹配.然后，如果不存在这些值，则A列中的值必须位于D列的最后一个单元格下面.如果这些值匹配，则不能进行复制和粘贴.

I have one Pivot table in column A and one normal table in column D. Pivot table will be updated every month. I would like to match column A values with column D. Then if the values are not existed then the values in Column A must come under the last cell in column D. If the values are matched then no copy and paste.

我需要如图所示的输出.我使用了下面的公式，但是它不起作用.显示循环参考误差.我在D13上使用了它. 我该如何解决.帮帮我

I need output as shown in image. I used the formula below but it is not working. It is showing circular reference error. I used it on D13. How can i solve it. Help me

在VBA中可以吗

我使用的Excel公式:

Excel formula which i used:

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

推荐答案

使用循环引用功能:

转到 File-> Options-> Formulas
在计算选项下选中启用迭代计算
将最大迭代次数设置为1
在Column D的第一个空白单元格中输入以下公式:

Using circular reference function:

Go to File->Options->Formulas
Under Calculation options tick Enable iterative calculations
Set maximum of iterations to 1
Input following formula at first blank cell in Column D:

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

将其向下拖动，直到获得所有需要的值.
请注意，您仍然会有空白单元格，无法在函数范围内将其删除.

Drag it down, until you have all needed values.
Note you will still have blank cells, it is impossible to delete them within functions' scope.

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

这篇关于匹配两列并获取匹配列以下的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！