CODEWITHSUNDEEP
arraysexcellookuptextjoinspill-range
dholt
dholt

Reputation: 69

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)

Upvotes: 2

Views: 1667

Answers (2)

Anonymous Coward
Anonymous Coward

Reputation: 1

What about a simpler function on each row, like we used to?

=TEXTJOIN(", ",TRUE,FILTER(RowData&" "&$Country$Codes, RowData))

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152450

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

Upvotes: 2

Related Questions