Macro to combine data from multiple rows if same identifier

NoSparks · #1 06-10-2019, 11:15 PM

The file with the copying all 12 months issue, in row 1, in which column is "Current Fcst" located ?

and is there anything else in row 1 ?

I'd prefer to be looking at a file rather than having to ask.

Tphil413 · #2 06-10-2019, 11:44 PM

In the current example the F-H is actuals and I-Q is Forecast. But that changes every month so ideally the code would be dynamic. Perhaps identify the forecast columns with zeros where the actuals would be copied? I have a file with better data but I'd prefer to send it to you than to post it. Thx

Tphil413 · #3 06-10-2019, 11:54 PM

See private message

NoSparks · #4 06-11-2019, 09:56 AM

You didn't answer the questions in post 16.

Anyway, try this.
Written cells are colored for visual identification.

Code:

Sub CombineData_v4()
    Dim i As Long, j As Long, lr As Long, helpCol As Long
Application.ScreenUpdating = False
With Sheets("Vendor Report")
    lr = .Range("S" & Rows.Count).End(xlUp).Row
    helpCol = 30    'AD
    For i = lr To 3 Step -1
        If .Cells(i, helpCol) = "A" And .Cells(i, "S") = .Cells(i - 1, "S") Then
            For j = 17 To 6 Step -1
                If .Cells(i, j) <> 0 Then
                    .Cells(i - 1, j) = .Cells(i, j)
                    .Cells(i - 1, j).Interior.ColorIndex = 17
                End If
            Next j
            .Rows(i).Delete
        End If
    Next i
End With
Application.ScreenUpdating = True
End Sub

Tphil413 · #5 06-11-2019, 10:51 PM

NoSpark,

Great news, the code ran perfectly against real data! The manual process that took me over an hour during a crunch time now completes in seconds. Thank you SO MUCH for your help. I really appreciate it.

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