Concatenate: in an IF Function
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" wrote in message
...
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
|