比较两个字符串并返回匹配的值? [英] Compare two strings and return matched values?

问题描述

我想比较两个相邻单元格中的两个字符串. 所有值均以逗号分隔. 返回用逗号分隔的匹配值.

I'm looking to compare two strings within two adjacent cells. All values separated by a comma. Returning the matched values separated by a comma.

有时，值重复多次，并且可以在字符串的不同部分.我列表中最大的字符串长度是6264.

Values are sometimes repeated more than once, and can be in different parts of the string. The largest string length in my list is 6264.

例如

Cell X2 = 219728401, 219728401, 219729021, 219734381, 219735301, 219739921

Cell Y2 = 229184121, 219728401, 219729021, 219734333, 216235302, 219735301

Result/Output = 219728401, 219729021, 219735301

我想将其应用于的单元格不仅限于X2和Y2，也可以是X列和Y列，输出到Z列(或我可以指定的列).

The cells I would like to apply this to is not limited to only X2 and Y2, it would be columns X and Y, with output into column Z (or a column I can specify).

由于我的VBA知识在Excel中受到限制，因此我对此表示感谢.

I appreciate any help with this, as my VBA knowledge is limited in Excel.

谢谢.

推荐答案

这是另一个使用Dictionary对象评估匹配项的版本.

Here's another version that uses a Dictionary object to assess matches.

它还使用数组来加快处理速度-对大型数据集很有用.

It also uses arrays to speed up the processing -- useful with large data sets.

请务必按照代码注释中的说明设置参考，但是如果您要分发此代码，则可能更喜欢使用后期绑定.

Be sure to set a reference as noted in the comments of the code, but if you are going to be distributing this code, you may prefer to use late-binding.

一个假设是您所有的值都是数字.如果某些内容包含文本，则您可能(或可能不希望)将字典的comparemode更改为Text.

One assumption is that all of your values are numeric. If some include text, you may (or may not) want to change the dictionary comparemode to Text.

Option Explicit
'Set reference to Microsoft Scripting Runtime

Sub MatchUp()
    Dim WS As Worksheet, R As Range
    Dim V, W, X, Y, Z
    Dim D As Dictionary
    Dim I As Long

Set WS = Worksheets("sheet1") 'Change to your desired worksheet
With WS
    'Change `A` to `X` for your stated setup
    Set R = .Range(.Cells(1, "A"), .Cells(.Rows.Count, 1).End(xlUp)).Resize(columnsize:=3)

    'Read range into variant array
    V = R
End With

For I = 2 To UBound(V, 1)
    W = Split(V(I, 1), ",")
    X = Split(V(I, 2), ",")
    V(I, 3) = ""

    'Test and populate third column (in array) if there are matches
    'Will also eliminate any duplicate codes within the data columns
    Set D = New Dictionary
        For Each Y In W
            Y = Trim(Y) 'could be omitted if no leading/trailing spaces
            If Not D.Exists(Y) Then D.Add Y, Y
        Next Y
        For Each Z In X
            Z = Trim(Z)
            If D.Exists(Z) Then V(I, 3) = V(I, 3) & ", " & Z
        Next Z
    V(I, 3) = Mid(V(I, 3), 3)
Next I

R.EntireColumn.Clear
R.EntireColumn.NumberFormat = "@"
R.Value = V 'write the results back to the worksheet, including column 3
R.EntireColumn.AutoFit
End Sub

这篇关于比较两个字符串并返回匹配的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！