CONCATENATE cells in a column IF condition in other colum is met
Using Google sheets (or Excel if Google sheets can't do it). I'm trying to concatenate any cell in B column, if its corresponding A cell is "include". The columns are very long and therefore I don't want to do a long list of IF statements.
| A | B |
|---|---|
| include | red |
| exclude | blue |
| include | green |
| exclude | yellow |
In this example, the results should be: redgreen
Solution 1:
In Excel 2019/365 you can do:
=TEXTJOIN(,TRUE,IF(A1:A4="include",B1:B4,""))
In Google Sheets:
=ArrayFormula(TEXTJOIN("",true,IF(A1:A4="include",B1:B4,"")))
For Excel 2010 (I think):
=CONCAT(IFERROR(INDEX(B1:B4,(2*(A1:A4="include")-1)*ROW(A1:A4),1),""))
entered with CRTL+SHIFT+ENTER because it is an array formula. If your data are not starting in A1, then you need to modify this more:
=CONCAT(IFERROR( INDEX(B1:B4,(2*(A1:A4="include")-1)*(ROW(A1:A4)-ROW(A1)+1),1),""))
Change the ranges to where A1:A4 and B1:B4 really are and set ROW(A1) to the first row of the data.
NB: replace formula comma separators with semi-colons (;) depending on Excel installed language.