VBA excel，有重复项时连接单元格 [英] VBA excel, concatenate cells when there are duplicates

问题描述

我在这里有一个像这样的矩阵

what I have here is a matrix like this

    id  value  
     1   A 
     2   B
     3   C
     1   D 
     3   E
     1   F

我需要做的是将值中的值求和，

What I need to do is to sum what I have in the value, having something along the lines of

    id  value  
     1   A, D, F 
     2   B
     3   C, E

删除重复项会很不错，但不是强制性的. 我在第三栏中尝试了此公式，但是...

Removing the duplicated it would be nice to have but not mandatory. I tried with this formula in a third column but...

 =IF(COUNTIF(A:A,A1)>1,CONCATENATE(B1,",",VLOOKUP(A1,A1:B999,2)),B1)   

VLOOKUP只是给我一个值，这意味着我不能处理超过1个重复项.

VLOOKUP just gives me back ONE value, it means that I cannot handle more than 1 duplicate.

我确实尝试过VBA，但这对我来说还是第一次，而且情况越来越复杂，而且我找不到关于excel VBA的体面文档.每个建议都表示赞赏.谢谢

I did try with VBA but it's the first time for me and it's getting complicated, furthermore I cannot find a decent documentation about excel VBA. every suggestion is appreciated. Thanks

推荐答案

此链接具有以下VBA功能可能会为您提供帮助:

This link with the following VBA function may help you:

Function vlookupall(sSearch As String, rRange As Range, _
    Optional lLookupCol As Long = 2, Optional sDel As String = ",") As String
'Vlookupall searches in first column of rRange for sSearch and returns
'corresponding values of column lLookupCol if sSearch was found. All these
'lookup values are being concatenated, delimited by sDel and returned in
'one string. If lLookupCol is negative then rRange must not have more than
'one column.
'Reverse("moc.LiborPlus.www") PB 16-Sep-2010 V0.20
Dim i As Long, sTemp As String
If lLookupCol > rRange.Columns.Count Or sSearch = "" Or _
    (lLookupCol < 0 And rRange.Columns.Count > 1) Then
    vlookupall = CVErr(xlErrValue)
    Exit Function
End If
vlookupall = ""
For i = 1 To rRange.Rows.Count
    If rRange(i, 1).Text = sSearch Then
        If lLookupCol >= 0 Then
            vlookupall = vlookupall & sTemp & rRange(i,lLookupCol).Text
        Else
            vlookupall = vlookupall & sTemp & rRange(i).Offset(0,lLookupCol).Text
        End If
        sTemp = sDel
    End If
Next i
End Function

这篇关于VBA excel，有重复项时连接单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！