ExcelBanter - Concatenate cells in one column of value in another column is same

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" wrote in message
ps.com...
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