SOLVED

# Merge Empty Data

Copper Contributor

# Merge Empty Data

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

3 Replies
best response confirmed by SergeiBaklan (MVP)
Solution

# Re: Merge Empty Data

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``````

# Re: Merge Empty Data

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

# Re: Merge Empty Data

Thanks so much for this!
1 best response

Accepted Solutions
best response confirmed by SergeiBaklan (MVP)
Solution

# Re: Merge Empty Data

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``````