Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

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
0
Unreal1998
Asked:
Unreal1998
  • 2
  • 2
1 Solution
 
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
 
Saurabh Singh TeotiaCommented:
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
 
Unreal1998Author Commented:
Great job
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now