填充excel字符串列以获得最快的后续vba搜索的最佳方法（我可以使用元数据等吗？）

In a column with hundreds or even 1-2 thousand strings of approximately 40 characters, with one string per cell and many repeating entries, what is the best way to populate the column to conduct the fastest possible search later? The search should return a row number so that the corresponding row can be deleted.

在包含数百甚至1-2千个大约40个字符的字符串的列中,每个单元格有一个字符串,并且有许多重复条目,填充列以便以后进行最快搜索的最佳方法是什么?搜索应返回行号,以便删除相应的行。

Is there some way to append metadata or label to a cell/row for faster search? Is there some other mechanism that can identify cells that will make searching easier?

有没有办法将元数据或标签附加到单元格/行以便更快地搜索?是否有其他机制可以识别将使搜索更容易的单元格?

I'm new to VBA, and I want to set out on the best path before I get too far into the project and have to search through thousands of strings.

我是VBA的新手,我想在走进项目太远之前就走出最佳路径并且必须搜索成千上万的字符串。

edit: Someone requested an example cell: The cells will have email addresses in them. I can control the email addresses on the server, so they will roughly be 40 characters long each. They will contain alphanumeric characters only.

编辑:有人请求示例单元格:单元格中将包含电子邮件地址。我可以控制服务器上的电子邮件地址,因此它们大约每个40个字符。它们仅包含字母数字字符。

1 个解决方案

#1

Example of a fast way to implement a dictionary lookup

实现字典查找的快速方法示例

Data is on Sheet1, and starts in column A

数据在Sheet1上,从A列开始

The strings are in column B

字符串在B列中

Option Explicit

Public Sub SearchStrings()

    Dim ur As Variant, r As Long, d As Object

    Const COL_ID = 2

    Set d = CreateObject("Scripting.Dictionary") 'or Reference to Microsof Scripting Runtime

    d.CompareMode = TextCompare 'Case insensitive, or "BinaryCompare" otherwise

    ur = Sheet1.UsedRange.Columns(COL_ID)   'read strings from column COL_ID into array

    For r = LBound(ur) To UBound(ur)        'populate dictionary; Key = string (unique)

        If Not IsError(ur(r, 1)) Then d(CStr(ur(r, 1))) = r      'Item = row id

    Next

    Debug.Print d.Keys()(3)     'prints the string in row 3
    Debug.Print d.Items()(3)    'prints the row number of the 3rd string

End Sub

If you want to store string duplicates use this:

如果要存储字符串重复项,请使用以下命令:

        If Not IsError(ur(r, 1)) Then d(COL_ID & "-" & r) = CStr(ur(r, 1))

which is Key = Column ID & "-" & row ID (2-5), and Item = String itself

这是Key =列ID和“ - ”&行ID(2-5),以及Item = String本身

#1