组合重复条目并将唯一数字数据相加

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

当遇到第一列中的类似值时,NickSlash写了一些关于如何在第二列中组合文本值的代码。我是一个完整的新手。基本上NickSlash编写的代码非常棒,除了我需要在第二列中添加数值。 NickSlash回答的问题可以在这里找到。将重复条目与Excel中的唯一数据组合在一起

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

NickSlash编写的代码如下,我想像这样的代码而不是电子表格中的公式。

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

1 个解决方案

#1

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

如果你想用代码而不是sumif来做这个,你应该只需要替换这一行

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

#1