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
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