?
Solved

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

Posted on 2009-05-15
4
Medium Priority
?
246 Views
Last Modified: 2012-05-07
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
Comment
Question by:Unreal1998
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 24398592
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
 

Author Comment

by:Unreal1998
ID: 24399014
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
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 2000 total points
ID: 24399054
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
 

Author Closing Comment

by:Unreal1998
ID: 31582027
Great job
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question