How to merge rows with same Name merging data of each related column

Question

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.

Answer 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