Macro to group sum of similar cells

**jamer02** · 02-27-2013, 05:37 PM

Hi

Here is an example worksheet:

Col A Col B
A 2
A 5
B 1
D 5
A 2
B 6
A 5

Now I am looking for a macro to group together all of the letters with the sum of each number beside that letter but this would be done in another sheet, so the above columns would still exist as they are on another sheet with the sorted elements separate. So, the outcome would be the following:

Col A Col B
A 14
B 7
D 5

Hope I have explained things clear enough. Any help would be most appreciated.

Thanks.

**AB33** · 02-27-2013, 05:46 PM

Change the name of sheet 8 and should have a name sheet called Output sheet

Sub jimmy()
Dim x, i As Long, P As Long, k As Long, n As Long
With Sheets("Sheet8").Range("a1").CurrentRegion
    x = .Value
With CreateObject("Scripting.Dictionary")
    .comparemode = 1
    For i = 1 To UBound(x)
        If .exists(x(i, 1)) Then
            n = .Item(x(i, 1))
             x(.Item(x(i, 1)), 2) = x(.Item(x(i, 1)), 2) + x(i, 2)
        Else
            P = P + 1
            .Item(x(i, 1)) = P
            For k = 1 To UBound(x, 2)
            x(P, k) = x(i, k)
            Next k
        End If
    Next i

End With
    With Sheets("Output")
    .Cells.ClearContents
    .Range("A1").Resize(P, UBound(x, 2)).Value = x
    .Range("A1").CurrentRegion.Sort key1:=Range("A2"), order1:=xlAscending, _
                         Header:=xlNo, DataOption1:=xlSortTextAsNumbers
    .Activate
    End With
End With