Macro to combine data from multiple rows if same identifier

NoSparks · #1 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

Tphil413 · #2 06-04-2019, 10:37 PM

Thanks NoSparks, I'll give this a try. I really appreciate your help!

Tphil413 · #3 06-05-2019, 12:30 AM

Hey NoSparks,

I tested the code and although it ran perfectly against the example data that I provided, the actual data file has a few subtle differences that seem to effect how the code runs.

1) The merged headers are not merged anymore (merge happens later in the code after all editing is completed)

2) Data contains negative values, not just positive values

3) All month columns now contain values, blanks are now zeros.

4) I've added helper data to all actuals rows in column AD as part of my report build so no need for your code to add the "A"s to actuals rows.

I've attached a better example file, can you take a look and see what needs to be updated in the code as a result of these changes? Thanks so much for your continued help. I feel like we're really close to a solve here.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to combine multiple rows in to one through an equation on EXCEL?	emitb	Excel	1	07-31-2018 04:22 PM
Combine multiple 'form data' into one all-encompassing Word document	Padraig Clarke	Word	1	04-19-2017 05:28 AM
Summarising data from multiple rows	mardecl1	Excel	5	01-06-2017 04:05 AM
Macro to Combine Multiple Rows into One Row	brunssl2	Excel Programming	10	01-13-2015 01:56 PM
data roll up under unique identifier	NewGirl	Mail Merge	1	11-15-2012 04:47 PM