SOLVED

# spilling conditional textjoin

Copper Contributor

# spilling conditional textjoin

Hello, I am needing some help to make a spilling conditional textjoin. I can do this in power query no worries but thats not an option in this case, it has to be done by formula and it must spill.

The basic requirement is I want to concatenate the values in B2# if the value in H1# matches the value in B1#

 A B C D E F G H I 1 Data Day1 Day1 Day1 Day2 Day2 Day1 Day2 2 Words join this text thank you join,this,text thank,you 3 Numbers 5 4 5 5 6 5,4,5 5,6 4 Words some more words to join some,more,words to,join

=MAKEARRAY(
ROWS(B2#),
COLUMNS(H1#),
LAMBDA(r,c,
BYROW(B2#,LAMBDA(a,TEXTJOIN(",",TRUE,IF(B1#=H1#,a))))
)
)

but clearly it doesn't work. Any assistance, much appreciated,
Cheers

9 Replies

# Re: spilling conditional textjoin

=BYROW(\$B\$2:\$F\$4,LAMBDA(x,TEXTJOIN(",",,FILTER(x,\$B\$1:\$F\$1=H1))))

An alternative could be this formula. The formula is in cell H6 in the example and filled to cell I6. # Re: spilling conditional textjoin

Hello, thanks, this is almost what I am needing. I need it to Spill based on H1# rather than H1 since the Number of unique Days will change

# Re: spilling conditional textjoin

I think I may need to see your sheet because it's not clear about H1 and if it will change?

This may be enough for you to take it and run with:

``````=LET(
seq, SEQUENCE(ROWS(data)),
REDUCE(
{"Day1", "Day2"},
seq,
LAMBDA(a, v,
LET(
day_1, TEXTJOIN(",", , FILTER(CHOOSEROWS(data, v), B1# = "Day1")),
day_2, TEXTJOIN(",", , FILTER(CHOOSEROWS(data, v), B1# = "Day2")),
VSTACK(a, HSTACK(day_1, day_2))
)
)
)
)``````

# Re: spilling conditional textjoin

In my understanding the formula in H1# spills arrays of e.g. Day1, Day2 or Day1, Day2, Day3 across range H1:I1 or H1:J1. However even if you shared the formula from cell H1# i probably couldn't suggest a formula that dynamically spills the results for 2 or 3 or more days (and across 2 or 3 or more columns).

A workaround could be with IFERROR.

=IFERROR(BYROW(\$B\$2:\$F\$4,LAMBDA(x,TEXTJOIN(",",,FILTER(x,\$B\$1:\$F\$1=H1)))),"")

This formula is in cell H6 and filled across range H6:Z6. Because of IFERROR empty cells are returned in range J6:Z8. If the formula in H1# spills Day1 to Day4 across range H1:K1 and these days are found in range B1:F1 then the results are returned in range H6:K8.

# Re: spilling conditional textjoin

Hello, I have attached a copy of the basic problem. Obviously, the cell references have changed but it shows the nature of what I am trying to achieve.
Cheers

# Re: spilling conditional textjoin

Hello, I have attached a copy of the basic problem to the OP. Obviously, the cell references have changed but it shows the nature of what I am trying to achieve.
Cheers

# Re: spilling conditional textjoin

With REDUCE:

``````=LET(
seq, SEQUENCE(ROWS(D13#)),
REDUCE(
seq,
LAMBDA(a, v,
LET(
Day_1, TEXTJOIN(",", , FILTER(CHOOSEROWS(D13#, v), D12# = "Day1")),
Day_2, TEXTJOIN(",", , FILTER(CHOOSEROWS(D13#, v), D12# = "Day2")),
VSTACK(a, HSTACK(Day_1, Day_2))
)
)
)
)``````

# Re: spilling conditional textjoin

Thanks for your time on this. The issue is that what happens when a third day comes in or a seventh? I am needing it to spill based on the unique days Day1...Dayn. I can sum etc using MAKEARRAY but the textjoining just seems far harder than is warranted.
Cheers
best response confirmed by samdthompson (Copper Contributor)
Solution

# Re: spilling conditional textjoin

If you're going to add more days, then maybe MAKEARRAY is the ticket:

``````=LET(
rows, ROWS(D13#),
uDays, UNIQUE(D12#, 1),
cols, COLUMNS(uDays),
MAKEARRAY(
rows,
cols,
LAMBDA(r, c,
LET(
d, INDEX(uDays, , c),
TEXTJOIN(",", , FILTER(CHOOSEROWS(D13#, r), D12# = d))
)
)
)
)``````