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

问题描述

我有两张 Excel 表格，其中一张包含用户列表.而另一个列表包含相同的数据，只是同一个用户被列出了几次.现在，我需要某种方法将第二个列表与第一个列表进行比较，并删除包含第一个列表中未找到的用户的行.

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 second list might look like this:

• 保罗·麦卡特尼
• 保罗·麦卡特尼
• 保罗·麦卡特尼
• 约翰·列侬
• 约翰·列侬
• 约翰·列侬
• 乔治·哈里森
• 乔治·哈里森
• 乔治·哈里森
• 林戈·斯塔尔
• 林戈·斯塔尔
• 林戈·斯塔尔
• 林戈之星
• 林戈之星
• 林戈之星

因此，比较这两个列表，您会看到名称 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屋！