Excel公式交叉参考2张，从一张表中删除重复 [英] Excel formula to Cross reference 2 sheets, remove duplicates from one sheet

问题描述

这与

Excel / VBA通​​过交叉引用2个不同的表格删除重复的行，然后删除1行

我似乎无法让任何VBA工作得很好或足够快，可以进行几百行。

I can't seem to get any VBA to work well or fast enough for a couple 100 rows.

Excel有一个公式可以从一个工作表中删除重复项，交叉引用另一张表？

Does Excel have a formula to remove duplicates from one sheet, by cross referencing another sheet?

感谢您的帮助。

推荐答案

这是一个更快的VBA解决方案，利用字典对象。正如你所看到的，它只循环一次通过表A和表B，而您的原始解决方案的运行时间与A的行数*表B中的行数成比例。

Here is a much faster VBA solution, utilizing a dictionary object. As you can see, it loops only once through sheet A and sheet B, while your original solution has a running time proportional to "number of rows in sheet A" * "number of rows in sheet B".

Option Explicit
Sub CleanDupes()
    Dim wsA As Worksheet
    Dim wsB As Worksheet
    Dim keyColA As String
    Dim keyColB As String
    Dim rngA As Range
    Dim rngB As Range
    Dim intRowCounterA As Integer
    Dim intRowCounterB As Integer

    keyColA = "A"
    keyColB = "B"

    intRowCounterA = 1
    intRowCounterB = 1

    Set wsA = Worksheets("Sheet A")
    Set wsB = Worksheets("Sheet B")

    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    Do While Not IsEmpty(wsA.Range(keyColA & intRowCounterA).Value)
        Set rngA = wsA.Range(keyColA & intRowCounterA)
        If Not dict.Exists(rngA.Value) Then
            dict.Add rngA.Value, 1
        End If
        intRowCounterA = intRowCounterA + 1
    Loop

    intRowCounterB = 1
    Do While Not IsEmpty(wsB.Range(keyColB & intRowCounterB).Value)
        Set rngB = wsB.Range(keyColB & intRowCounterB)
        If dict.Exists(rngB.Value) Then
             wsB.Rows(intRowCounterB).Delete
             intRowCounterB = intRowCounterB - 1
        End If
        intRowCounterB = intRowCounterB + 1
    Loop
End Sub

这篇关于Excel公式交叉参考2张，从一张表中删除重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！