Hi

Sorry that I could not even name this problem properly.

Please see tables below. The requirement will be quite obvious. Know
how to do this manually but not sure programmatically.

Thanks for the help.

Best
Prasad


Data Structure Before Macro

Name Place Animal Thing Number
Pers1 US Lion Pen One
Pers1 US Lion Pencil Two
Pers2 UK Bull Mouse Five


Data Structure After Macro
Name Place Animal Thing Number
Pers1 US Lion Pen, Pencil One, Two
Pers2 UK Bull Mouse Five

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 2 Step -1

If .Cells(i, TEST_COLUMN).Value = .Cells(i - 1,
TEST_COLUMN).Value Then

If .Cells(i, "B").Value <> .Cells(i - 1, "B").Value Then

.Cells(i - 1, "B").Value = .Cells(i - 1, "B").Value & ",
" & _
.Cells(i, "B").Value
End If

If .Cells(i, "C").Value <> .Cells(i - 1, "C").Value Then

.Cells(i - 1, "C").Value = .Cells(i - 1, "C").Value & ",
" & _
.Cells(i, "C").Value
End If

If .Cells(i, "D").Value <> .Cells(i - 1, "D").Value Then

.Cells(i - 1, "D").Value = .Cells(i - 1, "D").Value & ",
" & _
.Cells(i, "D").Value
End If

If .Cells(i, "E").Value <> .Cells(i - 1, "E").Value Then

.Cells(i - 1, "E").Value = .Cells(i - 1, "E").Value & ",
" & _
.Cells(i, "E").Value
End If

.Rows(i).Delete
End If
Next i

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"EE" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi
>
> Sorry that I could not even name this problem properly.
>
> Please see tables below. The requirement will be quite obvious. Know
> how to do this manually but not sure programmatically.
>
> Thanks for the help.
>
> Best
> Prasad
>
>
> Data Structure Before Macro
>
> Name Place Animal Thing Number
> Pers1 US Lion Pen One
> Pers1 US Lion Pencil Two
> Pers2 UK Bull Mouse Five
>
>
> Data Structure After Macro
> Name Place Animal Thing Number
> Pers1 US Lion Pen, Pencil One, Two
> Pers2 UK Bull Mouse Five
>

On Aug 18, 3:53 am, "Bob Phillips" <bob....@somewhere.com> wrote:
> Public Sub ProcessData()
> Const TEST_COLUMN As String = "A" '<=== change to suit
> Dim i As Long
> Dim iLastRow As Long
>
> With ActiveSheet
>
> iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
> For i = iLastRow To 2 Step -1
>
> If .Cells(i, TEST_COLUMN).Value = .Cells(i - 1,
> TEST_COLUMN).Value Then
>
> If .Cells(i, "B").Value <> .Cells(i - 1, "B").Value Then
>
> .Cells(i - 1, "B").Value = .Cells(i - 1, "B").Value & ",
> " & _
> .Cells(i, "B").Value
> End If
>
> If .Cells(i, "C").Value <> .Cells(i - 1, "C").Value Then
>
> .Cells(i - 1, "C").Value = .Cells(i - 1, "C").Value & ",
> " & _
> .Cells(i, "C").Value
> End If
>
> If .Cells(i, "D").Value <> .Cells(i - 1, "D").Value Then
>
> .Cells(i - 1, "D").Value = .Cells(i - 1, "D").Value & ",
> " & _
> .Cells(i, "D").Value
> End If
>
> If .Cells(i, "E").Value <> .Cells(i - 1, "E").Value Then
>
> .Cells(i - 1, "E").Value = .Cells(i - 1, "E").Value & ",
> " & _
> .Cells(i, "E").Value
> End If
>
> .Rows(i).Delete
> End If
> Next i
>
> End With
>
> End Sub
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "EE" <pras1...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > Hi
>
> > Sorry that I could not even name this problem properly.
>
> > Please see tables below. The requirement will be quite obvious. Know
> > how to do this manually but not sure programmatically.
>
> > Thanks for the help.
>
> > Best
> > Prasad
>
> > Data Structure Before Macro
>
> > Name Place Animal Thing Number
> > Pers1 US Lion Pen One
> > Pers1 US Lion Pencil Two
> > Pers2 UK Bull Mouse Five
>
> > Data Structure After Macro
> > Name Place Animal Thing Number
> > Pers1 US Lion Pen, Pencil One, Two
> > Pers2 UK Bull Mouse Five- Hide quoted text -
>
> - Show quoted text -

Many thanks.