delete duplicate then combine text

[gr_jafari]

I have thoes text in A1:A5 and now want to have that string in A6 , pleas any
help

ColumnA
1 book
2 pencil
3 pen
4 pen
5 book
6 book,pencil,pen

 

[Kevin B]

Just concatenate each cell value, excluding the duplicate, in cell A6
=A1&", "&A2&", "&A3&", "&A5

 

[gr_jafari]

Dear Kevin B , many thanks for answer , but i want to do that with a function
that operats automatically and flexible.

 

[gr_jafari]

in other word , i need a function that selects uniqe records of a long list
and combine them with a comma seprator in a cell

gr_jafari

 

[ShaneDevenshire]

Hi,

Here is a macro that does what you want:

Sub UniquesConcatenated()
Dim I As Integer
Dim cell As Range
Dim myAdd As String
Dim myCon As String

myAdd = ActiveCell.End(xlDown).Address
Selection.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("D1"), _
Unique:=True
I = 1
Range([D1], [D1].End(xlDown)).Select
For Each cell In Selection
If I = 2 Then
myCon = cell
ElseIf I > 2 Then
myCon = myCon & "," & cell
End If
I = I + 1
Next cell
Range(myAdd) = myCon
Selection.Clear
End Sub

As written it puts out a unique list into column D, so that range should be
empty or you should change the range in the macro. Also a title is required
as the first cell at the top of your data. And finally you must select the
data with the title before your run the macro.