Forum Discussion
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)) ) ) ) )
=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.
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.
- 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