在 Excel VBA 项目中匹配相似但不完全相同的文本字符串 [英] Matching similar but not exact text strings in Excel VBA projects

问题描述

好的，我一直在尝试为此找到解决方案，但似乎无法解决.我什至无法正确分解问题.这就是想法.

Okay, I have been trying to find a solution for this and I just don't seem to be able. I can't even break down the problem properly. This is the idea.

我有两张多行的工作表(一张有 800 行，另一张有 300,000 行).每行包含一个 Name 列，然后包含几个包含有关此 Name 的信息的列.每张纸都有不同种类的信息.

I have two sheets with many rows (one with 800 and the other with 300,000). Each row contains a Name column and then several columns that contain information about this Name. Each sheet has different kinds of information.

我想根据它们都有的 Name 列将这两个工作表合并到一个主工作表中，因此合并功能非常适合于此.现在的问题是名称不完全匹配.

I want to consolidate these two sheets into a master sheet based on this Name column they both have, so the consolidate function is perfect for this. Now the problem is that the names don't match perfectly.

例如 Sheet1 包含

For example Sheet1 contains

公司 B.V."、信息 #1"
公司总数"，信息#2"
公司有限公司"、信息 #3"

"Company B.V.", "Info #1"
"Company Total", "Info #2"
"Company Ltd", "Info #3"

和工作表 2 包含

公司和公司"，信息 #4"
公司和公司"，信息 #5"

"Company and Co.", "Info #4"
"Company and Co", "Info #5"

工作表 1 包含将要使用的所有名称(大约 100 个，但采用如上所述的不同形式)，工作表 2 包含多行中的所有这 100 个名称以及不在 100 个列表中的名称，因此我不在乎.

Sheet 1 contains all the names that are going to be used (around a 100 but in different forms as stated above) and sheet 2 contains all these 100 in multiple rows plus names that aren't in the 100 list and therefore I don't care about.

我将如何制作最终结果类似于这样的 VBA 代码项目，主表:

How would I make a VBA code project where the end result would be something like this, Master sheet:

公司"、信息#1"、信息#2"、信息#3"、信息#4"、信息#5"

"Company", "Info #1", "Info #2", "Info #3", "Info #4", "Info #5"

对于那里的每个公司"(100 家名单)??

for every single "Company" (the 100 list) in there??

我确实希望有解决方案.我对 VBA 项目很陌生，但我以前做过一些最少的编码.

I do hope there is a solution for this. I'm pretty new to VBA projects, but I have done some minimal coding before.

推荐答案

我会将宏放在您的个人部分，这样宏在所有工作表中都可用.通过录制虚拟宏并选择将其存储在个人宏工作簿中来执行此操作.现在您可以在此个人工作簿中手动添加新的宏和函数.

I would place the macro in your PERSONAL section, this way the macro is available in all worksheets. Do this by recording a dummy macro and select to store it in Personal Macro workbook. Now you can manually add new macro's and functions in this personal workbook.

我刚试过这个(不知道原始来源)，效果很好.

I just tried this one (don't know the original source) and it works fine.

公式如下所示:=PERSONAL.XLSB!FuzzyFind(A1,B$1:B$20)

The formula looks like this: =PERSONAL.XLSB!FuzzyFind(A1,B$1:B$20)

代码在这里:

Function FuzzyFind(lookup_value As String, tbl_array As Range) As String
Dim i As Integer, str As String, Value As String
Dim a As Integer, b As Integer, cell As Variant
For Each cell In tbl_array
  str = cell
  For i = 1 To Len(lookup_value)
    If InStr(cell, Mid(lookup_value, i, 1)) > 0 Then
      a = a + 1
      cell = Mid(cell, 1, InStr(cell, Mid(lookup_value, i, 1)) - 1) & Mid(cell, InStr(cell, Mid(lookup_value, i, 1)) + 1, 9999)
    End If
  Next i
  a = a - Len(cell)
  If a > b Then
    b = a
    Value = str
  End If
  a = 0
Next cell
FuzzyFind = Value
End Function

这篇关于在 Excel VBA 项目中匹配相似但不完全相同的文本字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！