Forum Discussion

Someone1's avatar
Someone1
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 the headings are     Dave Jim Tom 

as an example I want to use an x in the column beneath the headings to indicate dave and tom paid me but not jim and by marking an x in the respective column it will populate dave and tom's names in a separate cell.

Here is one formula that I have figured out so far but it only populates all the names if x is marked beneath each name. 

=CONCATENATE(IF(SEARCH("x",D3),$D$1,"")," ",IF(SEARCH("x",E3),$E$1,"")," ",IF(SEARCH("x",F3),$F$1,""))

Formulas and Functions

7 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 06, 2020

    Someone1 

    Your formula also works if use it as

    =CONCATENATE(
  IF(ISNUMBER(SEARCH("x",D3)),$D$1,"")," ",
  IF(ISNUMBER(SEARCH("x",E3)),$E$1,"")," ",
  IF(ISNUMBER(SEARCH("x",F3)),$F$1,"")
)

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 06, 2020

    Someone1 

    As a variant, in case your Excel recognises TEXTJOIN (Office365, Monthly Channel) you can combine it with FILTER (available to users in the same channel).

    =TEXTJOIN(", ",,FILTER(1:1,2:2="x"))

    where the headers are in row 1 and the  "x" in row 2. Of course, you may narrow down your ranges to, for example, D1:H1 and D2:H2.

  • DGuzmanG's avatar
    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's avatar
      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's avatar
        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's avatar
      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.

Resources