0

I need to merge rows with same Name preserving data of each column. For example, if we have two rows with the name "Mr Bean" on the first column "Name" then merge data of each column related to rows named "Mr Bean", then delete the second and third rows, putting all data in only the first row named "Mr Bean". So, we should have all data related to a single person in one row instead in more than one row.

+-------------------------------------------------------+
| Column Name   Column 2   Column 3   Column 4 Column 5 |
| Mr Bean        2           3                          |
| Mr Bean                    2          3        5      |
| Mr X           3                      3               |
| Mr Y           2           4          1        3      |
+-------------------------------------------------------+

Desired output:

+-------------------------------------------------------+
| Column Name  Column 2    Column 3   Column 4 Column 5 |
| Mr Bean        2         3, 2          3        5     |
| Mr X           3                       3              |
| Mr Y           2          4            1        3     |
+-------------------------------------------------------+

My Excel file has about 4000 rows and 450 columns.

1

1 Answer 1

1

This should do what you want, modify as you need -

Sub combine()
Application.ScreenUpdating = False
Dim c As Range
Dim i As Integer

For Each c In Range("A2", Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1))
Label:
If c = c.Offset(1) And c <> "" Then
       For i = 1 To 4
            If c.Offset(1, i) <> "" Then
                If c.Offset(, i) = "" Then
                c.Offset(, i) = c.Offset(1, i)
                Else: c.Offset(, i) = c.Offset(, i) & "," & c.Offset(1, i)
                End If
            End If
       Next
       c.Offset(1).EntireRow.Delete
       GoTo Label
End If

Next
Application.ScreenUpdating = True
End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.