Solved

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

Posted on 2009-05-15
Medium Priority
246 Views
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
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
• 2
• 2

LVL 59

Expert Comment

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
``````
0

Author Comment

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

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
``````
0

Author Closing Comment

ID: 31582027
Great job
0

## Featured Post

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.
###### Suggested Courses
Course of the Month10 days, 6 hours left to enroll