View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre[_3_] Ron Coderre[_3_] is offline
external usenet poster
 
Posts: 60
Default 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