Forum Discussion

Copper Contributor

Mar 06, 2020

Trying to figure out formula

Before typing out my problem thanks for taking a moment to help. I am trying to figure out what formula needs to be used in order to populate and combine data from multiple cells into one. Say ...

Formulas and Functions

DGuzmanG

Copper Contributor

Mar 06, 2020

Someone1Hello,

I've used the same case as you, to mark with an x (or any character) and retrieve the concatenated cell of the headings.

My formula is like this:

=IF(SUMPRODUCT(1*NOT(ISBLANK('Your-headings-range')))=0,"other",TEXTJOIN(", ",1,IF(therowrange="x",your-headings-range,"")))

I adapted it to exlude my info, but I must clarify that I used Table Names and not cells... Replace your headings range for $A$1:$A$3 and your row range, for the equivalent of the rows below for example: B1:B3.

NCRoy
Copper Contributor
Mar 06, 2020
DGuzmanG
Assuming Your Heading Range is A1:C1
and input Range is A2:C2

write below array formula in D2 and press ctrl + shift + enter

=TEXTJOIN(" ",1,IF((A2:C2="x"),$A$1:$C$1,""))
- DGuzmanG
  Copper Contributor
  Mar 09, 2020
  NCRoy
  
  But if you are using TEXTJOIN, it is because you are on Office 365 and with the recent dynamic arrange capabilities of Excel 365 it is not required to put Ctrl+Shift+Enter for array formulas anymore.
DGuzmanG
Copper Contributor
Mar 06, 2020
Someone1

You probably don't need the first part and only the TEXTJOIN formula, but it is a way to error-check.

The TEXTJOIN formula is better than CONCAT because it has the ability to ignore blanks. The first parameter is the separator you want to use, the second one is a boolean to ignore blanks (1 = True) and then you list and separate with comas all the texts you want to concatenate.