Excel: Group duplicate records into one row with all relevant comments combined.

I have a report in Excel that has repeating values in column B. I would like to combine all the values in the last column. See attached file. I hope I can find a formula for the "Comments" column that I can drag down for over 1K records.
Test-File.xls
Unreal1998Asked:
Who is Participating?
 
Saurabh Singh TeotiaConnect With a Mentor Commented:
You meant this...
Saurabh...

Sub delete()
    Dim i As Long
    i = 2
    Do Until i > Cells(65536, "B").End(xlUp).Row
        If Cells(i, "b").Value = Cells(i + 1, "b").Value Then
            Cells(i, "l").Value = Cells(i, "l").Value & "." & Cells(i + 1, "l").Value
            Cells(i, "m").Value = Cells(i, "m").Value & "." & Cells(i + 1, "m").Value
            Cells(i, "n").Value = Cells(i, "n").Value & "." & Cells(i + 1, "n").Value 
            Rows(i + 1).delete
        Else
            i = i + 1
        End If
    Loop
 
 
 
End Sub

Open in new window

0
 
Saurabh Singh TeotiaCommented:
Unreal,
Basis of your example, You can use the following code and it will do what you are looking for...
Saurabh...

Sub delete()
    Dim i As Long
    i = 2
    Do Until i > Cells(65536, "B").End(xlUp).Row
        If Cells(i, "b").Value = Cells(i + 1, "b").Value Then
            Cells(i, "l").Value = Cells(i, "l").Value & "." & Cells(i + 1, "l").Value
            Rows(i + 1).delete
        Else
            i = i + 1
        End If
    Loop
 
 
 
End Sub

Open in new window

0
 
Unreal1998Author Commented:
Wow! That's amazing!

Can you please customize the macro to do the same joining that was performed on column L to columns M and N at the same time? in other words, I want column M and N  to have comments from the related cells below, joined into one cell, just like L. I want all 3 done at the same time.

You are the genius!

0
 
Unreal1998Author Commented:
Great job
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.