Concatenate: in an IF Function

T

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
 
T

Teethless mama

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

copy down
 
T

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?
 
T

Teethless mama

You're Welcome!

Tracey said:
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?
 
S

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.
 
T

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
 
R

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top