Forum Discussion
Solved
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. ...
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.
- 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
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
