Consolidating multiple rows

[TheWennerWoman]

Hello,

I hope someone can help.

I have data which is sorted by property code (column B). There is always more than one row per property (sometimes 2, sometimes 4 or 5). Columns C-AA contain various financial metrics associated with the property.

What I would like to do is use a macro to loop through the data and sum the values in columns C-AA per property. After, I would need to zero these values apart from one of the rows.

Probably easier if I supply a small, cut-down sample with what I'm trying to do.

This is my before scenario:

Property Code Value1 Value2 Value3 Value4 Value5
ABCDEFGHIJK01  1000   200    1200   900    750
ABCDEFGHIJK01  1200   700    1000   400    550

And this is what the macro would do:

Property Code Value1 Value2 Value3 Value4 Value5
ABCDEFGHIJK01   0      0      0      0      0
ABCDEFGHIJK01  2200   900    2200   1300   1300

Any help greatly appreciated. Thank you for reading.

 

[Scott T]

try

Sub sumprop()

Dim lr As Long
Dim tot As Long

lr = Cells(Rows.Count, 2).End(xlUp).Row
For c = 3 To 27
    tot = 0
    For r = 2 To lr
        'If Cells(r, 2) = Cells(r - 1, 2) Or UCase(Cells(r - 1, 2)) = "PROPERTY CODE" Then
            tot = tot + Cells(r, c)
        'End If
        If Cells(r, 2) = Cells(r + 1, 2) Then
            Cells(r, c) = 0
        Else
            Cells(r, c) = tot
            tot = 0
        End If
    Next r
Next c

End Sub

 

[TheWennerWoman]

Outstanding Scott, that works exactly how I need it to.

You guys and your knowledge are awesome, what a gift you've got!

Thanks a lot (don't think this forum has the facility to mark Scott's reply as the answer).

 

[Scott T]

Your welcome.

Simply posting that a solution work is saying that the problem is solved.