excel-formulaexcel-2010

Excel conditional concatenation

I have a few columns that am trying to concatenate based off a conditional statement. The columns I have looks like:

Col 1    Col 2    Col 3    Col 4    Col 5
  73      NA       NA       NA       NA

Am trying to concatenate cells that do not have "NA" or are blanks. I have the following line of code:

=IF(OR(B2<>"",B2<>"NA"),CONCATENATE(A2,",",B2),IF(OR(C2<>"",C2<>"NA"),CONCATENATE(A2,",",B2,",",C2),IF(OR(D2<>"",D2<>"NA"),CONCATENATE(A2,",",B2,",",C2,",",D2),IF(OR(E2<>"",E2<>"NA"),CONCATENATE(A2,",",B2,",",C2,",",D2,",",E2),0))))

The desired output is 

73

However, I keep getting

 73, NA

Note: Column 1 will always have a number.

Solution

  • Try this instead:

    =LEFT(IF(AND(A1<>"",A1<>"NA"),A1 & ",","") & IF(AND(B1<>"",B1<>"NA"),B1 & ",","") &IF(AND(C1<>"",C1<>"NA"),C1 & ",","") &IF(AND(D1<>"",D1<>"NA"),D1 & ",","") &IF(AND(E1<>"",E1<>"NA"),E1 & ",",""),LEN(IF(AND(A1<>"",A1<>"NA"),A1 & ",","") & IF(AND(B1<>"",B1<>"NA"),B1 & ",","") &IF(AND(C1<>"",C1<>"NA"),C1 & ",","") &IF(AND(D1<>"",D1<>"NA"),D1 & ",","") &IF(AND(E1<>"",E1<>"NA"),E1 & ",","") )-1)

    enter image description here

    Edit:

    It appears as if your data loads sequentially by column. You can simplify the formula to:

    =A1 & IF(AND(B1<>"",B1<>"NA"),"," & B1,"")& IF(AND(C1<>"",C1<>"NA"),"," & C1,"")& IF(AND(D1<>"",D1<>"NA"),"," & D1,"")& IF(AND(E1<>"",E1<>"NA"),"," & E1,"")

    Edit #2

    If you are using Office 365 Excel then use TextJoin as an array formula:

    =TEXTJOIN(",",TRUE,IF((A1:E1<>"NA")*(A1:E1<>""),A1:E1,""))

    Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.