0

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 just gives me back ONE value, it means that I cannot handle more than 1 duplicate.

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

oroblam
  • 109
  • 4
  • 17

2 Answers2

3

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
Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
andrux
  • 2,782
  • 3
  • 22
  • 31
1

How about a pivot table :D and then copy the data to where ever you desire :D

This is another way if you want to give it a try :) specially if you do not want to use a function for each row but have a button click to output the data you desire (for a large dataset).

Sample Code: (you may set sheets, ranges according to yours)

Option Explicit

Sub groupConcat()
Dim dc As Object
Dim inputArray As Variant
Dim i As Integer

    Set dc = CreateObject("Scripting.Dictionary")
    inputArray = WorksheetFunction.Transpose(Sheets(4).Range("Q3:R8").Value)

       '-- assuming you only have two columns - otherwise you need two loops
       For i = LBound(inputArray, 2) To UBound(inputArray, 2)
            If Not dc.Exists(inputArray(1, i)) Then
                dc.Add inputArray(1, i), inputArray(2, i)
            Else
                dc.Item(inputArray(1, i)) = dc.Item(inputArray(1, i)) _ 
                & "," & inputArray(2, i)
            End If
       Next i

    '--output into sheet
    Sheets(4).Range("S3").Resize(UBound(dc.keys) + 1) = _ 
              Application.Transpose(dc.keys)
    Sheets(4).Range("T3").Resize(UBound(dc.items) + 1) = _ 
              Application.Transpose(dc.items)

    Set dc = Nothing
End Sub

Output:

enter image description here

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • @oroblam I understand you have choosen an answer :D in anycase if you are interested in give this a try. It will help you for a large data set for performance wise. As this reduces the interaction between ranges and code, do all processing in the code and them dump the data back to the sheet. – bonCodigo Jan 15 '13 at 18:40
  • @SiddharthRout thanks mate :) appreciated. I fancy MYSQL `Group Concat`, thought I would write my own here one for Excel. – bonCodigo Jan 17 '13 at 18:27
  • @user1987739 as per your question on how to use this method: copy this code in to a VBA MODULE. (Google for how to add a module). Add a button to your sheet where you have data and double click on the button. Then within `private sub commandbutton 1 _click ` event add this line: `Call GroupConcat`. Next change code's sheets(4) and ranges according to yours in the code such as `"Q3:R8"` which has input column data, `"S3"`, `"T3"` which are output columns starting cells. Compare code and screen :) – bonCodigo Jan 20 '13 at 09:16
  • I know this is an older answer, but I'm hoping I can get help. How can I do this for two columns that are not side by side? – DanCue Jul 18 '23 at 14:34
  • 1
    @DanCue try a pivot table and go from there to implement the above. Have you tried the accepted answer above? If it's too long a procedure, then you may want to post a question. (I think non-adjacent column case may not have been addressed before), so you will stand a better chance of a better solution and a support. Good luck. – bonCodigo Jul 19 '23 at 04:55
  • I've opened a question, thank you. – DanCue Jul 19 '23 at 11:36