Search code examples
arraysexcellookuptextjoinspill-range

Row-wise textjoin of dynamic array with lookup


This question is closely related to this answer from user mark fitzpatrick.

My sheet image

I have a variable list of country code headers in M3# and AB3# (same list) and a variable list of unique "mentions" as rows in A4#. The formulas in M4# and AB4# count the number of times a specific mention from A4# was listed for each country for two questions, "P" and "O" respectively. Each mention is present in at least one of "P" or "O". For each row in columns L and AA, I have a formula to textjoin each row's mention count per country for "P" and "O" separately (if no mentions, then "-"), but this is not a dynamic formula - I have to copy it down the range of mentions each time the range is updated.

I want columns L and AA to instead contain dynamic formulas. How do I update Mark's formula in columns AM and AN to contain country codes like in columns L and AA?

In AM4# and AN4#, I am using Mark's updated formula on my spilled ranges M4# and AB4# which produce the same result as my individual formulas, except without the country codes:

=LET( m, M4#,
   rSeq, SEQUENCE( ROWS(m) ),
   L, MMULT( LEN(m)--(m<>""), SIGN( SEQUENCE( COLUMNS(m) ) ) ) - 1,
   i, MMULT(--( TRANSPOSE( rSeq ) < rSeq ), L ) + rSeq,
   IFERROR( MID( TEXTJOIN( ",", TRUE, m ), i, L ), "" ) )

(edited to remove unnecessary intro)


Solution

  • concatenate the title to the range:

    =LET( m, M4#&IF(M4#<>""," "&$M$3:$T$3,""),
       rSeq, SEQUENCE( ROWS(m) ),
       L, MMULT( LEN(m)--(m<>""), SIGN( SEQUENCE( COLUMNS(m) ) ) ) - 1,
       i, MMULT(--( TRANSPOSE( rSeq ) < rSeq ), L ) + rSeq,
       IFERROR( MID( TEXTJOIN( ",", TRUE, m ), i, L ), "" ) )
    

    where $M$3:$T$3 is your titles.

    enter image description here


    Edit:

    To add the space we need to do some changes. I moved the desired splitter to its own variable so I can find the length of it:

    =LET( m, M4#&IF(M4#<>""," "&$M$3:$T$3,""),
       spl,", ",
       rSeq, SEQUENCE( ROWS(m) ),
       L, MMULT( LEN(m)+(m<>"")*LEN(spl), SIGN( SEQUENCE( COLUMNS(m) ) ) ) - LEN(spl),
       i, MMULT(--( TRANSPOSE( rSeq ) < rSeq ), L ) + ((rSeq-1)*LEN(spl)) + 1,
       IFERROR( MID( TEXTJOIN( spl, TRUE, m ), i, L ), "" ) )
    

    enter image description here