Merge Empty Data

[MDMorse]

In the spreadsheet below, I'm trying to remove duplicate "CPT Code" data, merging "Office Fee" and "Facility Fee" into one line for each CPT Code.

Note that there are some codes, like 11960 that don't need to be merged.

I'm using Office 365 Excel and can share the excel file if needed.

Thanks

[#2 · Accepted solution]

@MDMorse 

Here is a macro you can run. Please test on a copy of the worksheet!

Sub MergeRows()
    Dim s As Long
    Dim m As Long
    Dim t As Long
    Dim c As Long
    Dim vs As Variant
    Dim vt As Variant
    m = Range("A" & Rows.Count).End(xlUp).Row
    vs = Range("A1:D" & m).Value
    ReDim vt(1 To m, 1 To 4)
    For c = 1 To 4
        vt(1, c) = vs(1, c)
    Next c
    t = 1
    For s = 2 To m
        If vs(s, 1) <> vs(s - 1, 1) Then
            t = t + 1
            For c = 1 To 4
                vt(t, c) = vs(s, c)
            Next c
        Else
            vt(t, 3) = vs(s, 3)
        End If
    Next s
    Range("A1:D" & m).Value = vt
End Sub

[#3]

@MDMorse Alternatively, consider using Power Query. Once connected to the data, one single 'Group By' statement will do what you ask for. See attached.

[#4]

Thanks so much for this!