delete duplicate then combine text
 

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

delete duplicate then combine text
 

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


"gr_jafari" wrote:

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

delete duplicate then combine text
 

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

"Kevin B" wrote:
Just concatenate each cell value, excluding the duplicate, in cell A6
=A1&", "&A2&", "&A3&", "&A5
--
Kevin Backmann


"gr_jafari" wrote:

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

delete duplicate then combine text
 

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



"gr_jafari" wrote:

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

"Kevin B" wrote:
Just concatenate each cell value, excluding the duplicate, in cell A6
=A1&", "&A2&", "&A3&", "&A5
--
Kevin Backmann


"gr_jafari" wrote:

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

delete duplicate then combine text
 

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.
--
Thanks,
Shane Devenshire


"gr_jafari" wrote:

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