Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

This question already has an answer here:

Is there a way in Excel 2007 to group repeating rows (rows with the same value in one column) and concatenate the values from another column?

E.g.,

COL1  COL2

aaa     X
aaa     Y

Desired:

COL1  COL2

aaa    X,Y
share|improve this question

marked as duplicate by ChrisF, Dennis, Mokubai, 8088, Hennes Feb 7 '13 at 18:07

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

add comment

1 Answer

The best way to do this is to create a macro and then execute that macro whenever you need to. If you are unfamiliar with how to create macros check out Creating VBA Macros.

Try this:

Sub combineValues()
    Dim dic As Dictionary
    Dim key, val, i, p, k
    Set dic = New Dictionary
    For i = 1 To Worksheets(1).Range("A65536").End(xlUp).Row
        key = Worksheets(1).Cells(i, 1).Value
        val = Worksheets(1).Cells(i, 2).Value
        If dic.Exists(key) Then
            dic.Item(key) = dic.Item(key) & ", " & val
        Else
            dic.Add key, val
        End If
    Next
    p = 1
    For Each k In dic.Keys
        Worksheets(2).Cells(p, 1) = k
        Worksheets(2).Cells(p, 2) = dic.Item(k)
        p = p + 1
    Next
End Sub

Note that you'll have to include "Microsoft Scripting Runtime" in "Reference" in order to use Dictionary.

Tested with following [Sheet 1]:

1   value 1
2   value 2
3   value 3
1   value 4
1   value 5
3   value 6
3   value 7
2   value 8
1   value 9
2   value 10
2   value 11
2   value 12

Results in following [Sheet 2]:

1   value 1, value 4, value 5, value 9
2   value 2, value 8, value 10, value 11, value 12
3   value 3, value 6, value 7
share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.