Excel vba - 比较两个范围并查找不匹配项 [英] Excel vba - Compare two ranges and find non matches

问题描述

我有两个Excel工作表，其中一个工作表包含使用者清单。而另一个列表包含相同的数据，只有同一个用户被列出几次。现在，我需要一些方法将第二个列表与第一个列表进行比较，并删除包含在第一个列表中找不到的用户的行。

第一个列表如下所示：

• Paul Mccartney


• John Lennon


• 乔治·哈里森


• Ringo Starr

第二个列表可能如下所示：

• Paul Mccartney


• Paul Mccartney


• 
  
• 
  
• 
  
• 约翰·列侬


• 乔治·哈里森


• 乔治·哈里森


• 乔治·哈里森


• Ringo Starr


• Ringo Starr


• 
  
• Ringo Star


• Ringo Star

列表中，您会看到Ringo Star的名称不在第一个列表中，并且我要删除这些行。我试过几个循环，但我不能完全让这个工作。我想我可以将这些项目添加到某种数组，并通过一个函数运行它。

解决方案

这一次，你可以使用一个集合。 p>

这是根据您先前的档案进行的尝试：

  Option Explicit 
 
子测试（）
 Dim i As Long 
 Dim arrSum As Variant，arrUsers As Variant 
 Dim cUnique As New Collection 
 
'Put名称范围从数组中的摘要
 With ThisWorkbook.Sheets（Summary）
 arrSum = .Range（A2，.Range（A& Rows.Count）.End （xlUp））
 End With 
 
'将数组转换为集合（唯一项）
 For i = 1到UBound（arrSum，1）
 On Error Resume Next 
 cUnique.Add arrSum（i，1），CStr（arrSum（i，1））
下一个
 
'获取users数组
 With ThisWorkbook.Sheets（Users）
 arrUsers = .Range（A2，.Range（A& Rows.Count）.End（xlUp））
 End With 
 
'检查用户工作表中是否存在该值
 For i = 1 To cUnique.Count 
'如果在用户范围内找不到该值，请删除行
如果Application.WorksheetFunction.VLookup（cUnique（i），arrUsers，1，False）=＃N / A然后
 With ThisWorkbook.Sheets（Summary）。细胞
 .AutoFilter字段： 1，Criteria1：= cUnique（i）
。Range（A2，.Range（A& Rows.Count）.End（xlUp））。EntireRow.Delete 
以
结束如果
结束如果
结束如果还有
则删除AutoFilter ThisWorkbook.Sheets Summary）。AutoFilterMode = False 
 End Sub 
  

I've got two Excel sheets where one sheets consists of a list of users. And the other list contains the same data, only the same user is listed several times. Now, I need some way of comparing the second list with the first one and delete the rows that contains a user that's not found in the first list.

The first list looks like this:

• Paul Mccartney
• John Lennon
• George Harrison
• Ringo Starr

The second list might look like this:

• Paul Mccartney
• Paul Mccartney
• Paul Mccartney
• John Lennon
• John Lennon
• John Lennon
• George Harrison
• George Harrison
• George Harrison
• Ringo Starr
• Ringo Starr
• Ringo Starr
• Ringo Star
• Ringo Star
• Ringo Star

So, comparing these two lists, you see that the name Ringo Star is NOT in the first list, and I want to delete those rows. I've tried with several loops, but I can't quite get this to work. I guess I could add these items to an array of some sort, and run it though a function. But is there an easy way of doing this without that much code?

解决方案

This time, you could use a collection.

Here is a try based on your previous file:

Option Explicit

Sub test()
Dim i As Long
Dim arrSum As Variant, arrUsers As Variant
Dim cUnique As New Collection

'Put the name range from "Summary" in an array
With ThisWorkbook.Sheets("Summary")
    arrSum = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With

'"Convert" the array to a collection (unique items)
For i = 1 To UBound(arrSum, 1)
    On Error Resume Next
    cUnique.Add arrSum(i, 1), CStr(arrSum(i, 1))
Next i

'Get the users array
With ThisWorkbook.Sheets("Users")
    arrUsers = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With

'Check if the value exists in the Users sheet
For i = 1 To cUnique.Count
    'if can't find the value in the users range, delete the rows
    If Application.WorksheetFunction.VLookup(cUnique(i), arrUsers, 1, False) = "#N/A" Then
        With ThisWorkbook.Sheets("Summary").Cells
            .AutoFilter Field:=1, Criteria1:=cUnique(i)
            .Range("A2", .Range("A" & Rows.Count).End(xlUp)).EntireRow.Delete
        End With
    End If
Next i
'removes AutoFilter if one remains
ThisWorkbook.Sheets("Summary").AutoFilterMode = False
End Sub

这篇关于Excel vba - 比较两个范围并查找不匹配项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！