if row has duplicates, concat entries in another column?

[rchun]

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

[Claus Busch]

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

[rchun]

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

Claus Busch;1611836 Wrote:
> 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-

--
rchun

[shadi...@gmail.com]

Whoever wrote this macro just saved my life :D

[bobfla...@gmail.com]

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