Solved

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

Posted on 2009-05-15
4
229 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
  • 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 500 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now