SOLVED

Merge Empty Data

Copper Contributor

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

Fees.jpg

3 Replies
best response confirmed by SergeiBaklan (MVP)
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

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

Screenshot 2023-05-24 at 06.20.53.png

Thanks so much for this!
1 best response

Accepted Solutions
best response confirmed by SergeiBaklan (MVP)
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

View solution in original post