Concatenate: in an IF Function

[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 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

 

[Teethless mama]

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

copy down

 

[Tracey]

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]

You're Welcome!

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?

 

[Shane Devenshire]

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.

 

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

Thanks again for your help
Trace

 

[Ron Coderre]

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)