Microsoft Office Forums - View Single Post - [Solved] Macro to combine data from multiple rows if same identifier

NoSparks · 06-03-2019, 10:30 AM

The original suggestion did not distinguish between actual and forcast rows, it just combined them with the assumption that there would only be 2 rows and it didn't really matter if forcast or actual was first.

To accommodate the new scenarios a helper column is used to identify the actual rows.
Have used column 30 which is "AD" but you can easily adjust it if that's an issue.
This helper column is cleared at the end of the macro.
Row 1 merged cells for Actual and Current Fcst must be correct as it's the merged columns of Actual that things are based on.
Also now it does matter that the forcast row(s) precede the actual row.

Give this a try

Code:

Sub CombineData_v2()
    
    Dim i As Long, lr As Long
    Dim actCount As Long, helpCol As Long
    
Application.ScreenUpdating = False

With Sheets("Vendor Report")
    lr = .Range("S" & Rows.Count).End(xlUp).Row
    actCount = .Range("F1").MergeArea.Columns.Count
    helpCol = 30    'column 30 is column "AD"
    
    'determine if actual and identify in helpCol
    For i = lr To 3 Step -1
        If Application.Sum(.Range(Cells(i, 6), Cells(i, 6 + actCount))) > 0 And _
                Application.Sum(.Range(Cells(i, 6 + actCount + 1), Cells(i, 17))) = 0 Then
            .Cells(i, helpCol) = "A"
        End If
    Next i
    
    'loop again and only deal with actual rows
    For i = lr To 3 Step -1
        If .Cells(i, helpCol) = "A" Then
            If .Cells(i, "S") = .Cells(i - 1, "S") Then     'Dept-Account-Vendor match
                'copy actual to forcast
                .Cells(i - 1, "F").Resize(, actCount).Value = .Cells(i, "F").Resize(, actCount).Value
                'remove the actual row
                .Rows(i).Delete
            End If
        End If
    Next i
    
    'remove helper column
    .Columns(helpCol).ClearContents
    
End With

Application.ScreenUpdating = True
    
End Sub