if row has duplicates, concat entries in another column?

[rchun]

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

col1 col
Mark Robber
Mark Murde
Mark Arso
John Assaul
John DU
Dan Murde

The result I want is
col1 col
Mark Robbery, Murder, Arso
John Assault, DU
Dan Murde

Any help would be appreciated

 

[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

 

[rchun]

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

 

[shadih9274]

Whoever wrote this macro just saved my life

 

[bobflanagan1]

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!

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