在Excel 2010中，如何删除重复并连接包含多个值单元格的单元格范围内的值？ [英] In Excel 2010, how could I remove duplicates and concatenate values within a cell range that includes multiple values cells?

问题描述

我在Excel 2010中制作了一个文档，但是我希望从它获得的功能似乎是不可能的（至少不能使用默认的Excel函数），我对VB编程不够了解做我自己的UDF。 （我实际上是使用一个我在网上找到的，这是我想要的一部分，但不符合我的所有需求。）

让我把它分解下来：

1. 我有多张表格，用户可以添加数字（有些将为空白，有些将包含一个数字，有些将包含多个逗号分隔的数字）




2. 我有一个概述表格，我想连接这些数字（并删除任何重复的内容）几个不同的部分（仅查看特定的字段组）。

我发现一个ConcatIf UDF对于这个，但是它不能处理非连续的单元格连接（例如，我想将单元格D30，G30，J30和M30的重复项连接在一起）（以下是UDF：）

 函数ConcatIf（ByVal compareRange As Range，ByVal xCriteria As Variant，可选ByVal stringsRange As Range，_ 
可选分隔符作为St ring，可选NoDuplicates As Boolean）As String 
 Dim i As Long，j As Long 
 With compareRange.Parent 
设置compareRange = Application.Intersect（compareRange，Range（.UsedRange，.Range a1）））
结束
如果compareRange是Nothing然后退出函数
如果stringsRange没有，然后设置stringsRange = compareRange 
设置stringsRange = compareRange.Offset（stringsRange.Row -  compareRange.Row，_ 
 stringsRange.Column  -  compareRange.Column）
 
对于i = 1要比较Range.Rows.Count 
对于j = 1要compareRange.Columns.Count 
 If（Application.CountIf（compareRange.Cells（i，j），xCriteria）= 1）Then 
 If InStr（ConcatIf，Delimiter& CStr（stringsRange.Cells（i，j）））< 0 Imp Not（NoDuplicates）Then 
 ConcatIf = ConcatIf&分隔符CStr（stringsRange.Cells（i，j））
 End If 
 End If 
 Next j 
 Next i 
 ConcatIf = mid（ConcatIf，Len（Delimiter）+ 1）
结束函数
  

它也无法处理一个单元格中的多个数字作为单独的数字。

有没有办法使一个连续的UDF解析它正在查找的单元格，以查找多个单元格和单个数字单元之间的重复数字单元格，然后输出结果？最好允许它采取一系列不连续的单元格（跨不同的表单）。

对不起，如果解释有点复杂，这是我第一次问为这种帮助。 ：x

这是一个例子：

如果我有单元格：

• 2,4,6


• 2,6


• 2


• 4


• 6


• 6,8

我想要能够简单地得到：

• 2,4,6,8

现在，我会得到：

• 2,4,6,2,6,6,8

解决方案

尝试以下。如果您需要更改分隔符等，您可以适当地调整它。我已经记录了它在做什么以及为什么。

示例公式： = blah （A1：A7，A8，C9）（也可以从代码调用）

示例输出： 2 ，4,6,8

 公共函数Blah（ParamArray args（））As String 
'声明
 Dim uniqueParts As Collection 
 Dim area As Range 
 Dim arg，arr，ele，part 
 Dim i As Long 
 
'Initialisations 
设置uniqueParts =新集合
 
'枚举通过传递给此函数的参数
对于每个arg在args 
如果TypeOf arg是Range Then'range所以我们需要枚举它的.Areas 
对于每个区域在arg.Areas 
 arr = area.Value'中，对于大范围，一次加载数据会更快，而不是依次枚举每个单元
对于每个ele在arr'enumerat e数组
 addParts CStr（ele），uniqueParts'调用我们的子解析数据
下一个ele 
下一个区域
 ElseIf VarType（arg）> vbArray然后'一个数组已经在
中传递对于每个ele在arg'枚举数组
 addParts CStr（ele），uniqueParts'调用我们的子解析数据
下一个ele 
 Else'假设可以有效地转换为字符串。 
 addParts CStr（arg），uniqueParts'调用我们的子解析数据
 End If 
 Next arg 
 
'处理我们的结果
如果uniqueParts.Count> 0然后
 ReDim arr（0到uniqueParts.Count  -  1）
对于i = 1到uniqueParts.Count 
 arr（i  -  1）= uniqueParts（i）
下一个
'我们现在有一个数组的独特的部分，我们使用Join函数粘合在一起，然后返回
 Blah = Join（arr，，）
 End If 
 
结束函数
'Sub解析数据。在这种情况下，sub拆分字符串并将拆分元素添加到集合中，忽略重复
 Private Sub addParts（partsString As String，ByRef outputC As Collection）
'ByRef是不必要的，但我用它来记录那个outputC必须被实例化
 Dim part 
对于每个部分在Split（partsString，，）
 On Error Resume Next'现有的同一个键会引发错误，所以我们跳过它，只是执行
 outputC.Add part，part 
 On Error GoTo 0 
下一部分
 End Sub 
  

I made a document in Excel 2010 however, the functionality I'm hoping to get from it doesn't seem to be possible (at least not with the default Excel functions) and I don't know enough about VB programming to make my own UDF. (I'm actually using one I found online which does part of what I want, but doesn't meet all of my needs.)

Let me break it down:

1. I have multiple sheets with groups of fields where users can add numbers (some will be blank, some will contain a single number, some will contain multiple comma-separated numbers)

2. I have an "Overview" sheet where I want to Concatenate those numbers (and remove any duplicates) within a few different sections (only looking at specific field groups).

I found a ConcatIf UDF that works fairly well for this, however it can't handle non-consecutive cells to concatenate (For example, I want to concatenate and remove duplicates from cells D30, G30, J30 and M30 together) (Here's the UDF:)

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _ 
Optional Delimiter As String, Optional NoDuplicates As Boolean) As String 
Dim i As Long, j As Long 
With compareRange.Parent 
    Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1"))) 
End With 
If compareRange Is Nothing Then Exit Function 
If stringsRange Is Nothing Then Set stringsRange = compareRange 
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _ 
stringsRange.Column - compareRange.Column) 

For i = 1 To compareRange.Rows.Count 
    For j = 1 To compareRange.Columns.Count 
        If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then 
            If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then 
                ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j)) 
            End If 
        End If 
    Next j 
Next i 
ConcatIf = mid(ConcatIf, Len(Delimiter) + 1) 
End Function 

It also can't handle the "multiple numbers in one cell" as separate numbers.

Is there a way to make a Concatenate UDF that "parses" the cells it's looking at to look for duplicates between the multiple numbers cells and the single numbers cells, and then output the result? Preferably allowing it to take a series of non-consecutive cells to work on (across different sheets).

Sorry if the explanation is a bit convoluted, it's my first time asking for this kind of help. :x

Here's an example:

If I have cells with:

• 2,4,6
• 2,6
• 2
• 4
• 6
• 6,8

I'd want to be able to simply get:

• 2,4,6,8

Right now, instead, I'd get:

• 2,4,6,2,6,6,8

解决方案

Try the below. You can adapt it appropriately if you need to change the delimiter etc. I have documented what it is doing and why.

Example formula: =blah(A1:A7,A8,C9) (it can also be called from code)

Example output: 2,4,6,8

Public Function Blah(ParamArray args()) As String
    'Declarations
    Dim uniqueParts As Collection
    Dim area As Range
    Dim arg, arr, ele, part
    Dim i As Long

    'Initialisations
    Set uniqueParts = New Collection

    'Enumerate through the arguments passed to this function
    For Each arg In args
        If TypeOf arg Is Range Then 'range so we need to enumerate its .Areas
            For Each area In arg.Areas
            arr = area.Value 'for large ranges it is greatly quicker to load the data at once rather than enumerating each cell in turn
                For Each ele In arr 'enumerate the array
                     addParts CStr(ele), uniqueParts 'Call our sub to parse the data
                Next ele
            Next area
        ElseIf VarType(arg) > vbArray Then 'an array has been passed in
            For Each ele In arg 'enumerate the array
                addParts CStr(ele), uniqueParts 'Call our sub to parse the data
            Next ele
        Else 'assume can be validly converted to a string. If it cannot then it will fail fast (as intended)
            addParts CStr(arg), uniqueParts 'Call our sub to parse the data
        End If
    Next arg

    'process our results
    If uniqueParts.Count > 0 Then
        ReDim arr(0 To uniqueParts.Count - 1)
        For i = 1 To uniqueParts.Count
            arr(i - 1) = uniqueParts(i)
        Next i
        'we now have an array of the unique parts, which we glue together using the Join function, and then return it
        Blah = Join(arr, ",")
    End If

End Function
'Sub to parse the data. In this case the sub splits the string and adds the split elements to a collection, ignoring duplicates
Private Sub addParts(partsString As String, ByRef outputC As Collection) 
'ByRef is unecessary but I use it to document that outputC must be instantiated
    Dim part
    For Each part In Split(partsString, ",")
        On Error Resume Next 'existing same key will raise an error, so we skip it and just carry on
        outputC.Add part, part
        On Error GoTo 0
    Next part
End Sub

这篇关于在Excel 2010中，如何删除重复并连接包含多个值单元格的单元格范围内的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！