Create Multiple Values From Other Columns

  • Thread starter Thread starter Daz
  • Start date Start date
D

Daz

Hi Guys, I trying to create a comma sepearted list in a cell based on
the values in three columns (each column is a reference for a different
sector of our business - if they are not a client in one sector, the
cell is blank).

This is the formula i am using at the moment:

=IF(J2<>"","CY",IF(K2<>"","CT",IF(P2<>"","F","")))

Only problem is if there is a number in more than one column, it only
registers the first column, therefore only showing the contact as a
client of only one part of our business.

How can I get it to display mutliple values, One for each sector they
belong to (pref comma seperated?)

TIA

Daz

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software! Free Support at
http://www.ozgrid.com/forum/ **
 
Hi Daz

How about
=IF(J2<>"","CY, ","")&IF(K2<>"","CT, ","") & IF(P2<>"","F","")

Cheers
JulieD
 
actually slight change to the formula to only ensure you get a comma if
there's another value following it
=IF(OR(K2<>"",P2<>""),IF(J2<>"","CY,
",""),IF(J2<>"","CY",""))&IF(P2<>"",IF(K2<>"","CT, ",""),IF(K2<>"","CT",""))
& IF(P2<>"","F","")

Cheers
JulieD
 
Hi Daz

always welcome questions .. the formula breaks down like this:
=IF(OR(K2<>"",P2<>""), #check to see if there is anything going to follow CY
IF(J2<>"","CY, ",""), #if there is then check to see whether they belong to
CY and place a comma after the CY (hence "CY, ")
IF(J2<>"","CY","")) #if there isn't going to be anything following the CY
just put the CY in
& #this is the symbol we use to join text strings together ... so we either
have nothing CY, or CY to join to the next statement
IF(P2<>"", #check to see if there is anything going to follow CT
IF(K2<>"","CT, ",""), #if there is put put a comma after CT
IF(K2<>"","CT","")) #if there isn't, just put CT
& so now we have the result of the first IF and the second IF to join to the
third
IF(P2<>"","F","") #which then determines the last value of our string

if you want to put other tests in here, here' how you do 1 (A1) more ...
just follow the same pattern for more:
=IF(OR(K2<>"",P2<>"",A1<>""),IF(J2<>"","CY,
",""),IF(J2<>"","CY",""))&IF(OR(P2<>"",A1<>""),IF(K2<>"","CT,
",""),IF(K2<>"","CT",""))
& IF(A1<>"",IF(P2<>"","F,",""),IF(P2<>"","F",""))&IF(A1<>"","X","")

Hope this helps
Cheers
julieD
 
Back
Top