if row has duplicates, concat entries in another column?
 

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!

if row has duplicates, concat entries in another column?
 

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!

if row has duplicates, concat entries in another column?
 

Whoever wrote this macro just saved my life :D

if row has duplicates, concat entries in another column?
 

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