Row-wise textjoin of dynamic array with lookup

Question

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)

BTW: commenting on a previous answer to get guidance on your own problem is not appropriate. You have done the appropriate thing by asking your own question regardless of rep. — Scott Craner, Jul 30 '21 at 16:16
Thanks for the guidance, I'll be sure to make my own posts in the future as well. — dholt, Jul 30 '21 at 17:22

Scott Craner · Accepted Answer · 2021-07-30T17:39:49.523

2

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.

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 ), "" ) )

edited Jul 30 '21 at 17:39

answered Jul 30 '21 at 16:30

Scott Craner

148,073
10
49
81

Thank you, this is very close to what I'm after! Can you share an edit that will result in "1 B, 1 C" with a space between the comma and "1 C", but no extra space if there is only 1 country code? – dholt Jul 30 '21 at 17:21
This is excellent, but....looks like in the new formula you pasted, the comma after spl,"," is missing. Your formula in the screenshot does work though. Thank you! Marked as answer. – dholt Jul 30 '21 at 17:39
@dholt you are correct I missed it. Edited to add. – Scott Craner Jul 30 '21 at 17:40

score 0 · Answer 2 · edited Oct 16 '21 at 14:34

0

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

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

edited Oct 16 '21 at 14:34

ChrisGPT was on strike

127,765
105
273
257

answered Oct 16 '21 at 14:10

Anonymous Coward

1

Row-wise textjoin of dynamic array with lookup

2 Answers2

Linked

Related