Forum Discussion

Clause9's avatar
Clause9
Copper Contributor
Jan 24, 2023
Solved

Create a list of columns with value per row

Subject probably makes no sense so let me explain with an example:

 A.1A.2A.3A.4A.5A.6A.7A.8DESIRED RESULT
#0011  34   A.1, A.4, A.5
#002       1A.8
#003113  2  A.1, A.2, A.3, A.6
#004   2    A.4
#005      1 A.7
#006 3  3  3A.2, A.5, A.8
#007  2     A.3
#008 2 1   2A.2, A.4, A.8

 

The desired result illustrated in the table will probably be in another tab of the worksheet. Oh, the commas as seperators are optional 🙂🙂

 

What form of lookup formula do I need to do to get the header value and how do I ensure that only cells with a value are considered? 

 

Thanks in advance

 

 

excel
Formulas and Functions
office 365
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 24, 2023

    Clause9 

    =BYROW(B2:I9,LAMBDA(row,TEXTJOIN(",",,FILTER(B1:I1,NOT(ISBLANK(row))))))

    You can try this formula.

     

    • Clause9's avatar
      Clause9
      Copper Contributor
      Jan 25, 2023

      OliverScheurich Vielen Dank!!!

       

      That works perfectly - I won't pretend to follow the logic or understand what a LAMBDA function is but the formula does exactly what I asked. I've even managed to transpose it so I get the IDs per column as follows:

      =BYCOL(B2:I10,LAMBDA(column,TEXTJOIN(CHAR(10),TRUE,FILTER(A2:A10,NOT(ISBLANK(column))))))

       

      Your help is very much appreciated!

      • Clause9's avatar
        Clause9
        Copper Contributor
        Jan 25, 2023
        Blast; I spoke too soon 😞

        The formula works perfectly in the example I provided however, the real use case is slightly different.

        A table similar to my example is one tab of a workbook named Assessment, the IDs are in cells A7:A130, the header values are in cells L7:CZ7 so the range of cells is L7:CZ130.

        Another tab named SOA contains a list of the header values in 'Assessment'!L7:CZ7 in Col A (cells A7:A95). The SOA tab contains a range of information associated with each header value in Cols B:K.

        I'd like to get the IDs per header value column using the transposed formula in my last reply and insert these into a new column in the SOA tab.

        I put the example formula into cell 'SOA'!L7 and it returned the expected results but these are inserted into 'SOA'!L7:CZ7. I need the IDs going down Col L from L3:L95 aligned with the relevant entry from 'Assessment'!L7:L130

        I've tried simply changing to BYROW which does fill Col L but only returns a "#VALUE!" error.
        My amended formula in 'SOA'!L3 is:
        =BYROW('Assessment'!L7:CZ130,LAMBDA(column,TEXTJOIN(",",TRUE,FILTER('Assessment'!A7:A130,NOT(ISBLANK(column)))))).

        Sorry for not explaining my use case properly; I incorrectly assumed that transposing the desired results would have little impact on the formula.

        thanks in advance if anyone can help

Resources