Combining duplicate entries and adding together unique numerical data

Question

I have two columns of data. I would like to find all of the similar values in the first column and then add together the numerical values in the associated second column. There are hundreds of values in random order.

NickSlash wrote some code on how to combine text values in the second column when similar values in the first column are encountered. I am a complete novice. Basically the code NickSlash wrote is awesome, except I have numerical values that need to be added together in the second column. That question NickSlash answered can be found here. Combining duplicate entries with unique data in Excel

The code NickSlash wrote is below, I would like code like this as opposed to formulas in the spreadsheet.

Option Explicit

Sub Main()
Dim Source As Worksheet: Set Source = ThisWorkbook.Worksheets("Sheet1")
Dim Destination As Worksheet: Set Destination = ThisWorkbook.Worksheets("Sheet2")

Dim Records As Object: Set Records = CreateObject("Scripting.Dictionary")

Dim Data As Variant
Dim Index As Long
Dim Row As Integer: Row = 1

Data = Source.Range("A1", "B" & Source.Rows(Source.UsedRange.Rows.Count).Row).Value2

For Index = LBound(Data, 1) To UBound(Data, 1)
    If Records.Exists(Data(Index, 1)) Then
        Destination.Cells(Records(Data(Index, 1)), 2).Value2 = Destination.Cells(Records(Data(Index, 1)), 2).Value2 & ", " & Data(Index, 2)
    Else
        Records.Add Data(Index, 1), Row
        Destination.Cells(Row, 1).Value2 = Data(Index, 1)
        Destination.Cells(Row, 2).Value2 = Data(Index, 2)
        Row = Row + 1
    End If
Next Index

Set Records = Nothing

End Sub

score 0 · Accepted Answer · answered Nov 25 '13 at 22:51

If you want to do this with code instead of sumif you should just need to replace this line

Destination.Cells(Records(Data(Index, 1)), 2).Value2 = Destination.Cells(Records(Data(Index, 1)), 2).Value2 & ", " & Data(Index, 2)

with

    Destination.Cells(Records(Data(Index, 1)), 2).Value2 = Destination.Cells(Records(Data(Index, 1)), 2).Value2 + Data(Index, 2)

Gordon

Combining duplicate entries and adding together unique numerical data

1 Answers1