Este código VBA deve funcionar
Public Sub combineRows()
Dim wkb As Workbook
Dim wks, wks1 As Worksheet
'Define variables
titleRow = 1
namecolumns = 2
Set wkb = ThisWorkbook
Set wks = wkb.Sheets("Sheet1") 'Source sheet
Set wks1 = wkb.Sheets("Sheet2") 'Destination sheet
'Clear the destination Sheet
wks1.Rows.Clear
'Count rows and columns on source sheet
totalrows = wks.Cells(Rows.Count, 1).End(xlUp).row
totalcolumns = wks.Cells(titleRow, columns.Count).End(xlToLeft).Column
'Copy title row
wks.Rows(titleRow).Copy wks1.Rows(titleRow)
wks1row = titleRow + 1
'Iterates each row on source sheet
For i = titleRow + 1 To totalrows
original = concnames(wks, i, namecolumns)
totalrowswks1 = wks1.Cells(Rows.Count, 1).End(xlUp).row
coincidence = False
'Check if the same name exists on Destination sheet
For k = titleRow + 1 To totalrowswks1
originalwks1 = concnames(wks1, k, namecolumns)
If original = originalwks1 Then
coincidence = True
k = totalrowswks1
End If
Next k
'If the name exists on destination skips it
If coincidence = False Then
'Copy the entire row to destination
For j = 1 To totalcolumns
wks1.Cells(wks1row, j) = wks.Cells(i, j)
Next j
'Check on source other rows with the same name to copy its data
For j = i + 1 To totalrows
other = concnames(wks, j, namecolumns)
If other = original Then
For k = namecolumns + 1 To totalcolumns
theCell = wks.Cells(j, k)
If theCell <> "" Then
wks1.Cells(wks1row, k) = theCell
End If
Next k
End If
Next j
wks1row = wks1row + 1
End If
Next i
End Sub
Public Function concnames(ByVal SheetName As Worksheet, therow, thecolumns)
'This function concatenates the values on the namecolumns to create
'a single one string.
'It makes very easy to compare rows.
originalvalue = ""
For m = 1 To thecolumns
cellData1 = SheetName.Cells(therow, m)
originalvalue = originalvalue & cellData1
Next m
concnames = originalvalue
End Function
Abra VBA / Macros com ALT + F11 , em ThisWorkbook insira um novo Módulo e cole o código do lado direito.
Verifique no código que as variáveis titleRow e namecolumns coincidem com o seu caso, bem como os nomes das folhas, e execute-as.
