Concatenate With Criteria

[Tracey]

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 Concatenated (should Read):
Row 1: United Kingdom France Japan United Kingdom Italy United Kingdom,
France , Japan, Italy
Row 2: USA USA USA USA
Row 3: France Singapore Italy Spain France France, Singapore, Italy, Spain,
Row 4: United Kingdom France United Kingdom United Kingdom United
Kingdom United Kingdom, France


the following formula works to a point...
=SUBSTITUTE(TRIM(CONCATENATE(A1," ",B1," ",C1," ",D1," ",E1))," ",", ")

it brings back the following for Row 4 (mentioned above)

Row 4: United, Kingdom, France, United, Kingdom, United, Kingdom, United,
Kingdom

Where I would like it to read: United Kingdom, France

Is this possible?

Many thanks
Tracey

 

[Luke M]

Shane's answer should work. To clarify, you need to put the coding into the
VBA (Press Alt+F11) and paste to either the sheet you're working on, or
insert a module and paste to that. Reposted:
**********
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
***********************

 

[Shane Devenshire]

Hi,

Please see my response to your previous post.

 

[Tracey]

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 in the VBA part? (obviously I've changed the
data range from
A1:E1 to the correct range)

Thanks again for your help
Trace
******************************************

 

[Luke M]

Oops, I fear I led you astray. You need to post the code into a module
(Insert-Module), not into the regular sheet coding.

My apologies.

 

[Tracey]

You guys are Fabulous!!!!!

Thank you!!

Oops, I fear I led you astray. You need to post the code into a module
(Insert-Module), not into the regular sheet coding.

My apologies.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*