I'm new to Excel, and struggling with compiling multiple duplicate rows, example:

col1 col2
Mark Robbery
Mark Murder
Mark Arson
John Assault
John DUI
Dan Murder

The result I want is:
col1 col3
Mark Robbery, Murder, Arson
John Assault, DUI
Dan Murder

Any help would be appreciated!

Hi,

Am Tue, 14 May 2013 21:20:25 +0100 schrieb rchun:

col1 col2
Mark Robbery
Mark Murder
Mark Arson
John Assault
John DUI
Dan Murder

The result I want is:
col1 col3
Mark Robbery, Murder, Arson
John Assault, DUI
Dan Murder

try it with a macro. Press Alt+F11 = Insert = Module and paste the
code in the code window:

Sub Test()
Dim LRow1 As Long
Dim LRow2 As Long
Dim i As Long
Dim j As Long
Dim rngC As Range
Dim c As Range
Dim firstAddress As String
Dim myStr As String

j = 1
LRow1 = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LRow1
If WorksheetFunction.CountIf(Range(Cells(1, 1), _
Cells(i, 1)), Cells(i, 1)) = 1 Then
Cells(j, 3) = Cells(i, 1)
j = j + 1
End If
Next

LRow2 = Cells(Rows.Count, 3).End(xlUp).Row
For Each rngC In Range("C1:C" & LRow2)
myStr = ""
With Range("A1:A" & LRow1)
Set c = .Find(rngC, after:=Cells(LRow1, 1), LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
myStr = myStr & c.Offset(0, 1) & ", "
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
rngC.Offset(0, 1) = Left(myStr, Len(myStr) - 2)
End If
End With
Next
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

awesome, thank you! that's pretty complicated, but it worked!

Whoever wrote this macro just saved my life :D

On Tuesday, May 14, 2013 4:20:25 PM UTC-4, rchun wrote:
I'm new to Excel, and struggling with compiling multiple duplicate rows,

example:



col1 col2

Mark Robbery

Mark Murder

Mark Arson

John Assault

John DUI

Dan Murder



The result I want is:

col1 col3

Mark Robbery, Murder, Arson

John Assault, DUI

Dan Murder



Any help would be appreciated!









--

rchun

The Merge Assistant, http://www.add-ins.com/product_list.htm will do exactly what you want done