excel 连接“唯一”值的VBA代码

omvjsjqw 于 2023-02-10 发布在其他

关注(0)|答案(1)|浏览(133)

我试图写一个VBA代码，将从一个选择的值和连接的单元格值与行分隔符。我也希望不包括和重复。
例如：
假设我有一个如下所示的数据集，我想输入=ConcatenateUnique（A1：B2，"，”）并让它返回One，Two，Three
| 色谱柱A|B栏|
| - ------|- ------|
| 一个|两个|
| 三个|一个|
我尝试了下面的方法，虽然我知道如果它工作，它只会返回二，三

Function CONCATENATEUNIQUE(Ref As Range, Separator As String) As String
Dim Cell As Range
Dim Result As String
For Each Cell In Ref
If WorksheetFunction.CountIf(Ref, Cell.Value) <= 1 Then
Result = Result & Cell.Value & Separator
End If
Next Cell
CONCATENATEMULTIPLE = Left(Result, Len(Result) - 1)
End Function

excel

来源：https://stackoverflow.com/questions/75392401/vba-code-that-concatenates-unique-values

1条答案

按热度按时间

hwazgwia1#

对于这类任务，脚本字典很有用（但请注意，这在Mac上不起作用）：

Function UniqueList(rng As Range, Optional sep As String = ",")
    Dim arr, r As Long, c As Long, v, dict As Object
    If rng.Count = 1 Then 'handle single-cell case
        UniqueList = rng.Value
        Exit Function
    End If
    arr = rng.Value 'get values into an array
    Set dict = CreateObject("Scripting.Dictionary")
    For r = 1 To UBound(arr, 1)
    For c = 1 To UBound(arr, 2)
        v = arr(r, c)
        If Not IsError(v) Then
            If Len(v) > 0 Then dict(v) = True
        End If
    Next c
    Next r
    UniqueList = Join(Application.Transpose( _
                       Application.Transpose(dict.Keys)), sep)
End Function

赞(0）回复(0）举报 2023-02-10

我来回答

excel 连接“唯一”值的VBA代码

1条答案

相关问题

热门标签

最新问答