CODEWITHSUNDEEP
excelvba
FloIancu
FloIancu

Reputation: 2725

Excel: Sum adjacent rows with same value

I have a table with the following structure:

Timestamp    Name    Value
01/01/2013   X       20
01/01/2013   Y        5
01/01/2013   Y        3
01/01/2013   X        7
01/02/2013   X       15
01/02/2013   X        1  
01/02/2013   Z        8
01/03/2013   Y        1

I want to sum the Value of adjacent rows with the same Name into the first row (keeping the same Timestamp and other values on the row, only the Value is updated). There can be any number of successive occurrences, I've only put two for the sake of the example. The result of the above example should look like this:

Timestamp    Name    Value
01/01/2013   X       20
01/01/2013   Y        8
01/01/2013   X       23
01/02/2013   Z        8
01/03/2013   Y        1

Upvotes: 0

Views: 172

Answers (1)

Liniel
Liniel

Reputation: 729

Sub SumAdjacent()
Dim TimeStamp As Date
Dim Name As String
Dim Value As Integer
k = 1
For i = 2 To 80 'range of your data from secound row to 80row(or whatewer you want)
    Value = 0
    TimeStamp = Sheets(1).Cells(i, 1).Value
    Name = Sheets(1).Cells(i, 2).Value
    Value = Sheets(1).Cells(i, 3).Value
    j = i + 1
    'check next row
CheckNextRow:
    If Sheets(1).Cells(j, 2) = Name Then
        Value = Value + Sheets(1).Cells(j, 3).Value
            j = j + 1
            GoTo CheckNextRow ' loop to check every next row value
    Else
        Sheets(2).Cells(k, 1).Value = TimeStamp
        Sheets(2).Cells(k, 2).Value = Name
        Sheets(2).Cells(k, 3).Value = Value
        k = k + 1
                i = j - 1
    End If
Next
End Sub

Upvotes: 1

Related Questions