Excel combine rows dollar amount with matching row values

I would resolve this with the Power Query Add-In. It takes a few steps to get there and a bit of coding in the Power Query language (M) to generate the "running count" needed to get the "cost1/2/3" column headings, and more M code to call the Table.Pivot function (it's not exposed in the Power Query UI).

I've built a prototype which you can view or download - its "Power Query demo - Pivot rows into columns with Running Count.xlsx" in my One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

Basically my technique was to add a calculated column to get the "Cost Title" e.g. cost1/2/3. To get this I needed to write a "Running Count" function, to return an Index that resets for each group (title).

I got the outline for the "Running Count" function from this blog post - under "Year-to-Date Sales":

http://cwebbbi.wordpress.com/2013/10/18/implementing-common-calculations-in-power-query/

Then I used the Table.Pivot function to generate a column for each unique value in the destination column.

The documentation for Table.Pivot is here:

http://office.microsoft.com/en-au/excel-help/table-pivot-HA104111995.aspx?CTT=5&origin=HA104122363

Another example of using Table.Pivot is here:

http://cwebbbi.wordpress.com/2013/11/25/pivoting-data-in-power-query/

Finally I filtered out the rows with nothing for cost3.

It's a pretty rough macro but

Option Explicit

Sub CombineRowsRevisited()

'c is a CELL or a range
Dim c As Range
'i is a number
Dim i As Integer

'for each CELL in this range
For Each c In Range("A2", Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1))
'if the CELL is the same as the cell to the right AND
'if the cell 4 to the right is the same as the cell below that one
If c = c.Offset(1) And c.Offset(, 4) = c.Offset(1, 4) Then
            'then make the cell 3 to the right the same as the cell below it
            c.Offset(, 3) = c.Offset(1, 3)
            'and delete the row below the CELL
            c.Offset(1).EntireRow.Delete
End If

Next

End Sub

This would be easier to understand given the above

Sub CombineRowsRevisitedAgain()
    Dim myCell As Range
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    For Each myCell In Range(Cells("A2"), Cells(lastRow, 1))
        If (myCell = myCell.Offset(1)) And (myCell.Offset(0, 4) = myCell.Offset(1, 4)) Then
            myCell.Offset(0, 3) = myCell.Offset(1, 3)
            myCell.Offset(1).EntireRow.Delete
        End If
    Next
End Sub

However, depending on the problem, it might be better to step -1 on a row number so nothing gets skipped.

Sub CombineRowsRevisitedStep()
    Dim currentRow As Long
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For currentRow = lastRow To 2 Step -1
        If Cells(currentRow, 1) = Cells(currentRow - 1, 1) And _
        Cells(currentRow, 4) = Cells(currentRow - 1, 4) Then
            Cells(currentRow - 1, 3) = Cells(currentRow, 3)
            Rows(currentRow).EntireRow.Delete
        End If
    Next

End Sub