Excel / 有效识别(精确)特定表列(35K 行)中字符串的出现

Question

我有一个包含 35000 行的表，每行应该有一个唯一的（字母数字）ID，但我需要检查这一点。 使用 countif 需要计算年龄，什么是最有效的（最少的计算时间）。

[ID] | [Occurences]
[A7A8S8D9] | [<formula>]
[F6F7F8F9] | [<formula>]

Answer 1

如果您

不介意VBA解决方案，这会将"重复"放在除第一个以外的所有重复ID旁边。

我在使用 =RANDBETWEEN(1, 1000) 创建的 35,000 个数字 ID 上对其进行了测试，它在 671 毫秒内运行。 如果将范围传递到数组中，它可能会运行得更快。

'Remove line to remove timing.
Private Declare Function GetTickCount Lib "kernel32" () As Long
'Will not mark first occurrence as duplicate.
Public Sub CheckForDuplicates()
    Dim rng As Range
    Dim rCell As Range
    Dim dict As Object
    'Remove next two lines to remove timing.
    Dim TC As Long
    TC = GetTickCount
    'Update sheet name as required.
    With ThisWorkbook.Worksheets("Sheet1")
        'Defines range as A2 to last row containing data (providing there's no empty cells in col A).
        '-1 at end of line as we're starting on row 2.
        Set rng = .Range("A2").Resize(.Cells(Rows.Count, 1).End(xlUp).Row - 1)
    End With
    Set dict = CreateObject("Scripting.Dictionary")
    With dict
        For Each rCell In rng
            If .Exists(rCell.Value) Then
                rCell.Offset(, 1) = "Duplicate"
            Else
               .Add rCell.Value, rCell.Value
            End If
        Next rCell
    End With
    'Remove line to remove timing.
    MsgBox GetTickCount - TC & "ms elapsed."
End Sub