Hi,

If I read your question correctly, you don't want to concatenate entries
that are repeats and I think that is what the current function does. In your
example UK is only concatenated once.

If that is what you really want to do then try the following custom function:

Function myConCatenate(myRange As Range) As String
I = 1
For Each cell In myRange
If I = 1 Then
myCon = cell
ElseIf cell < "" Then
For J = I - 1 To 1 Step -1
If cell < cell.Offset(0, -J) Then
OK = True
Else
OK = False
Exit For
End If
Next J
If OK = True Then
myCon = myCon & ", " & cell
End If
End If
I = I + 1
Next cell
myConCatenate = myCon
End Function

In the spreadsheet you would enter

=myConCatenate(A1:E1)

A couple of points, this only concatenates uniques, and it will work for any
number of columns.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Tracey" wrote:

Thank you...

if
A B C
D E
row 1: United Kingdom France France United Kingdom
Spain

that formula brings back: United, Kingdom , France, France, United, Kingdom,
Spain

where it should read: United Kingdom, France, Spain

Is this possible?

"Teethless mama" wrote:

=SUBSTITUTE(TRIM(CONCATENATE(A1," ",B1," ",C1," ",D1," ",E1))," ",", ")

copy down


"Tracey" wrote:

hello

I have 5 columns that I need to concatenate IF there is a value and if the
value isnt already in the other cells i.e:

A B C D E Conatenated:
UK France Japan UK Italy UK, France , Japan, Italy
USA USA
France Singapore Italy Spain France, Singapore, Italy, Spain,
UK France UK, France



Any help will be appreciated

Thank you!
T