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

=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

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

You're Welcome!

"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

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

Hi Shane,

Thanks for that... I've done that (I think I'm being a little daft) but I
get #NAME? when I run that...
do I need to change anything (obviously I've changed the data range from
A1:E1 to the correct range)

Thanks again for your help
Trace

"Shane Devenshire" wrote:

> 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

With
A1:E1 always containing 5 text values
Example:
A1: United_Kingdom
B1: France
C1: United_Kingdom
D1: United_Kingdom
E1: United_Kingdom

This formula returns the unique values, concatenated into one cell
F1: =A1&IF(MATCH(B1,A1:E1,0)=COLUMN(B1),", "&B1,"")&
IF(MATCH(C1,A1:E1,0)=COLUMN(C1),", "&C1,"")&
IF(MATCH(D1,A1:E1,0)=COLUMN(D1),", "&D1,"")&
IF(MATCH(E1,A1:E1,0)=COLUMN(E1),", "&E1,"")

In the above example, the formula returns: United_Kingdom, France

Is that something you can work with?

Regards,

Ron Coderre
Microsoft MVP (Excel)

"Tracey" <(E-Mail Removed)> wrote in message
news:A3D523EF-1470-4B07-BED2-(E-Mail Removed)...
> 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