Forum Discussion

Bogie27's avatar
Bogie27
Copper Contributor
Aug 10, 2021

Concatenating arguments with the IF function

I'm using MS Excel 2016 (part of Office Professional Plus 2016)

I have following spreadsheet.

 

IDplan1plan2plan3plan4plan5plan6Result
12345BH1DoNothingBH3DoNothingDoNothingDoNothingBH1;BH3
12255DoNothingDoNothingDoNothingDoNothingDoNothingBH6BH6
12667DoNothingDoNothingBH3BH4DoNothingDoNothingBH3;BH4
13984BH1BH2BH3BH4BH5BH6BH1;BH2;BH3;BH4;BH5;BH6
13999BH1DoNothingDoNothingBH4BH5BH6BH4;BH5;BH6
14016DoNothingDoNothingDoNothingBH4DoNothingBH6BH4;BH6
14555DoNothingBH2DoNothingDoNothingBH5DoNothingBH2;BH5
14578DoNothingDoNothingDoNothingDoNothingDoNothingBH6BH6
14580DoNothingDoNothingDoNothingDoNothingDoNothingDoNothing 

 

In column H, I want to have a concatenated list of the data in all columns, separated by a semicolon, for a given row where the row does not have "DoNothing".  So the data only concatenates into the end result field if it is not "DoNothing".  Also would prefer to not have the semicolon after the last item in the result, but that is the least of my worries.

 

I've tried combining the Concatenate and IF functions like this: =CONCATENATE(=IF(B2<>"DoNothing",B2 & ";",""),=IF(B3<>"DoNothing",B3 & ";",""))

 

I'm trying to get the end result by using criteria based on a range of fields (IF B2:G2<>"DoNothing"), then concatenate the data from that field into the end result, separated by a semicolon.

 

My thought was to concatenate the result of both IF statements, but it doesn't work.  If I use the IF statement by itself, it does work, but I need to concatenate it with the other columns in the result.

 

Is there a better way to get this to work, instead of doing the IF statement individually for each column?  I'm thinking there has to be a way.

  • Bogie27's avatar
    Bogie27
    Aug 10, 2021

    Detlef_Lewin Thank you so much Detlef.  This worked perfectly.  I wasn't aware of the TEXTJOIN function and didn't see it with all of my web searches.  I'll keep it handy.  

    • Bogie27's avatar
      Bogie27
      Copper Contributor

      Detlef_Lewin Thank you so much Detlef.  This worked perfectly.  I wasn't aware of the TEXTJOIN function and didn't see it with all of my web searches.  I'll keep it handy.  

    • HalloWorld's avatar
      HalloWorld
      Copper Contributor

      Detlef_Lewin 

       

      This answer gives you the chance to change the second "B2:G2" to another row and check against empty cells. I changed it to the header row to join all of the headings as soon as the cell was not empty: `=TEXTJOIN(";",TRUE,IF(B2:G2<>"",B$1:G$1,""))`. You need to execute it with `Ctrl+Shift+Enter`.

Resources