比较两列与另外两列，并找到匹配以及非匹配 [英] Compare two columns with two other columns, and find matches as well as non-matches

问题描述

我试图找出如何循环两个数组，将第二个数组中的所有值与第一个数组中的所有值进行比较，对于每个匹配，测试列D中同一行的值和列H相等。

在我的第一个数组中，我有这样的数据。

在我的第二个数组中，我有这样的数据。

所以，对于OW单元格，我想比较

OW&报告实体，并确保两者都是VARCHAR。这将是这些字段名称的相同数据类型的匹配。另外，对于OW&货币，都是VARCHAR，所以再次匹配。对于OW& CID_CounterpartyID，我看到整数和VARCHAR，所以这不是一个匹配，我想知道这个。

我试图把东西放在一起做，我在下面显示我的代码，但这绝对不起作用。

  Sub CompareFinal（）
 
 Dim r1 As Range 
 Dim r2 As Range 
 Dim cell As Range 
 Dim lastrow As Long 
 
'第一个数组中的所有行
使用ThisWorkbook.Worksheets（数据验证）
 lastrow = .Cells （.Rows.Count，A）。End（xlUp）.Row 
设置r1 = .Range（A2：A& lastrow）
结束
 
 '第二个数组中的所有行
使用ThisWorkbook.Worksheets（数据验证）
 lastrow = .Cells（.Rows.Count，E）。End（xlUp）.Row 
设置r2 = .Range（E2：E& lastrow）
结束
 
'循环通过第二个数组，它将不等于或大于First Array 
对于每个单元格在r2 
如果IsError（Application.Match（cell，r1，0））然后
 cell.Offset（，5）=Match
 Else 
 cell.Offset（， 5）=NoMatch
结束如果
下一个单元格
 
 End Sub 
  

我确定我在代码中过分简化了一些事情，这给我错误的结果。有人可以帮我弄清楚吗？无论是非代码功能还是VBA解决方案，我都会做这个工作。

谢谢。

解决方案

我只是意识到我实际上需要再匹配两列。下面的功能似乎是我想要的。

  = ISNUMBER（MATCH（B2&|​​& C2& |& D2，$ E $ 2：$ E $ 800& $ | $ H $ 2：$ H $ 800&|​​&$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $  

我发现这个链接非常有用。

a href =https://stackoverflow.com/questions/22978075/compare-two-columns-if-another-two-columns-are-matching-in-excel-macro>如果另外两列匹配，则比较两列在excel宏

希望这有助于别人。

I’m trying to figure out how to loop through two arrays, compare all values in the second array to all the values in the first array, and for each match, test whether the value in the same row, in Column D and Column H, are equal.

In my first array I have data like this.

In my second array, I have data like this.

So, for the OW cells, I want to compare

OW & Reporting Entity, and make sure both are VARCHAR. This would be a match of the same data type for these field names. Also, for OW & Currency, both are VARCHAR, so again, match. For OW & CID_CounterpartyID, I see Integer and VARCHAR, so this is not a match, an I want to be aware of this.

I tried to put something together to do this, and I'm displaying my code below, but this definitely does not work.

Sub CompareFinal()

    Dim r1 As Range
    Dim r2 As Range
    Dim cell As Range
    Dim lastrow As Long

    ' All Rows in First Array
    With ThisWorkbook.Worksheets("Data Validation")
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set r1 = .Range("A2:A" & lastrow)
    End With

    ' All rows in Second Array
    With ThisWorkbook.Worksheets("Data Validation")
        lastrow = .Cells(.Rows.Count, "E").End(xlUp).Row
        Set r2 = .Range("E2:E" & lastrow)
    End With

    ' Loop through Second Array, which will bequual to or longer than First Array
    For Each cell In r2
        If IsError(Application.Match(cell, r1, 0)) Then
            cell.Offset(, 5) = "Match"
        Else
            cell.Offset(, 5) = "NoMatch"
        End If
    Next cell

End Sub

I'm sure I'm over-simplifying things in the code, and this is giving me incorrect results. Can someone help me get this straightened out? Either a non-code function, or a VBA solution would do the job, I think.

Thanks.

解决方案

I just realized that I actually need to match one two more columns. The function below seems to do what I want.

=ISNUMBER(MATCH(B2 & "|" & C2 & "|" &D2,$E$2:$E$800 & "|" & $H$2:$H$800 & "|" & $I$2:$I$800,0))

I found this link to be very helpful.

Compare two columns if another two columns are matching in excel macro

Hope this helps others.

这篇关于比较两列与另外两列，并找到匹配以及非匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！